SQL Server Create parent child XML using Select

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

稿源:Chanmingman's Blog (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » SQL Server Create parent child XML using Select

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录