在SQL Server中解析多级XML(Parsing a MultiLevel XML in SQL Server)

在SQL Server中解析多级XML(Parsing a MultiLevel XML in SQL Server)

我需要解析具有多级多元素的XML。

示例XML：

<Studies> <Study ID="1"> <Site Id="1"> <Participant ID="111"/> <Participant ID="222"/> </Site> <Site Id="2"> <Participant ID="333"/> <Participant ID="444"/> </Site> </Study> <Study ID="2"> <Site Id="3"> <Participant ID="555"/> <Participant ID="666"/> </Site> <Site Id="4"> <Participant ID="777"/> <Participant ID="888"/> </Site> </Study> </Studies>

我试过了：

SELECT StudyID = XC.value('@ID', 'int'), SiteId = XC2.value('@Id', 'int'), ParticipantId = XC3.value('@ID', 'int') FROM @Xml.nodes('//Study') AS XT(XC) CROSS APPLY xc.nodes('Site') AS XT2(XC2) CROSS APPLY xc.nodes('Participant') AS XT3(XC3)

这不返回任何数据。 如果我改变

xc.nodes('Participant') AS XT3(XC3)

至

xc.nodes('//Participant') AS XT3(XC3)

它返回32行。

我期待8行。 有什么指针吗？

I need to parse an XML with multi-level multiple elements.

A sample XML:

<Studies> <Study ID="1"> <Site Id="1"> <Participant ID="111"/> <Participant ID="222"/> </Site> <Site Id="2"> <Participant ID="333"/> <Participant ID="444"/> </Site> </Study> <Study ID="2"> <Site Id="3"> <Participant ID="555"/> <Participant ID="666"/> </Site> <Site Id="4"> <Participant ID="777"/> <Participant ID="888"/> </Site> </Study> </Studies>

I have tried:

SELECT StudyID = XC.value('@ID', 'int'), SiteId = XC2.value('@Id', 'int'), ParticipantId = XC3.value('@ID', 'int') FROM @Xml.nodes('//Study') AS XT(XC) CROSS APPLY xc.nodes('Site') AS XT2(XC2) CROSS APPLY xc.nodes('Participant') AS XT3(XC3)

This returns no data. If I change

xc.nodes('Participant') AS XT3(XC3)

to

xc.nodes('//Participant') AS XT3(XC3)

it returns 32 rows.

I am expecting 8 rows though. Any pointers?

最满意答案

SELECT StudyID = XC.value('../../@ID', 'int'), SiteId = XC.value('../@Id', 'int'), ParticipantId = XC.value('@ID', 'int') FROM @Xml.nodes('//Studies/Study/Site/Participant') AS XT(XC) SELECT StudyID = XC.value('../../@ID', 'int'), SiteId = XC.value('../@Id', 'int'), ParticipantId = XC.value('@ID', 'int') FROM @Xml.nodes('//Studies/Study/Site/Participant') AS XT(XC)