SQL Server Create parent child XML using Select

存储架构 2017-11-15

I have a parent and child table in SQL Server database.

The parent table name ATAHeader

SELECT Header . txnDateTime , Header . [fileName] FROM ATAHeader Header

The child table name ATA

SELECT ATAInformation . [MCKey] , ATAInformation . [MCValue] , ATAInformation . SCN FROM [dbo] . [ATA] ATAInformation

We would like to produce the XML hierarchy like the one below.

< Header >

< txnDateTime > 2017-09-25T13:35:41 txnDateTime >

< fileName > REQ_MYTPP-J33-S_JATA_20170925133541.xml fileName >

< ATAInformation >

< MCKey > MCKey >

< MCValue > MCValue >

< SCN > 152292 SCN >

ATAInformation >

Header >

You will need to follow the SQL statement below to produce the XML.

SELECT Header . txnDateTime , Header . [fileName] ,
ATAInformation . [MCKey] , ATAInformation . [MCValue] , ATAInformation . SCN
FROM [dbo] . [ATAHeader] Header

inner

join

[dbo]

.

[ATA] ATAInformation


on Header . fileName = ATAInformation . fileName

FOR

XML

AUTO , ELEMENTS

责编内容by:Chanmingman's Blog (源链)。感谢您的支持!

您可能感兴趣的

Select two values ​&ZeroWid... I have three tables table1, table2, table3. table1 has columns a, b. Table 2 has...
Fast XML Parser – Validate or Parse XML to J... fast-xml-parser Validate XML or Parse XML to JS/JSON very fast without C/C++...
MySQL入门学习笔记——七周数据分析师实战作业... 本篇推送主要涉及SQL语言中较为复杂的子查询与函数嵌套。 虽然这个MySQL系列取名为MySQL基础入门,但是个人不打算做单个函数的用法总结,或者...
Oracle CCI and OJAudit SonarQube Plugins In this blog I introduce to you two sonarQube plugins for Oracle FMW products. ...
Too many value errors in SQL I am trying to answer the following question in oracle: List the names of empl...