分析、过滤TSQL中的嵌套XML

Question

我有一个表(table1(，它有一列包含XML数据。我需要解析XML并从元素的子元素创建数据行——输出需要类似TestID序列ParentSequence ExtID ExtName-1 1-1 ABC-1 2-1 1定义-1 2-1 GHI

但我尝试过的所有其他方法都得到了一个空的结果集
我专注于访问序列，因为其余部分将遵循相同的过程。不确定为什么这不起作用。感谢在这方面提供的任何帮助。非常感谢。我尝试的SQL是在XML之后(注释文本是我尝试的选项(

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmlfield 
NVARCHAR(MAX));
INSERT INTO @tbl (xmlfield) VALUES
(N'<OBJECT CLASS="Test1" ID="-1" FULL="FULL" VERSION="1">
<SUBTYPE NAME="SubType1">
<OBJECT NAME="SubType111" ID="-1">
<FIELD NAME="TestID">-1</FIELD>
<FIELD NAME="Sequence">1</FIELD>
<FIELD NAME="ParentSequence">-1</FIELD>
<FIELD NAME="ExtID">-1</FIELD>
<FIELD NAME="ExtName">ABC</FIELD>
</OBJECT>
<OBJECT NAME="SubType111" ID="-1">
<FIELD NAME="TestID">-1</FIELD>
<FIELD NAME="Sequence">2</FIELD>
<FIELD NAME="ParentSequence">1</FIELD>
<FIELD NAME="ExtID">-1</FIELD>
<FIELD NAME="ExtName">DEF</FIELD>
<FIELD NAME="__ExtendedData">&lt;OBJECT 
CLASS="Meet123" ID="-1" FULL="FULL" 
VERSION="1"&gt;&lt;FIELD 
NAME="OrderDetailID"&gt;-1&lt;/FIELD&gt;&lt;FIELD 
NAME="OrderID"&gt;-1&lt;/FIELD&gt;&lt;FIELD 
NAME="Sequence"&gt;0&lt;/FIELD&gt;&lt;FIELD 
NAME="AttendeeID"&gt;123&lt;/FIELD&gt;&lt;FIELD NAME=" 
AttendeeID _Name"&gt;Test, Mark/I H 6&lt;/FIELD&gt;&lt;FIELD 
NAME="ShowList"&gt;1&lt;/FIELD&gt;&lt;FIELD 
NAME="BdgeName"&gt;Mark&lt;/FIELD&gt;&lt;FIELD 
NAME="BadgeCompanyName"&gt;I H 6&lt;/FIELD&gt;
&lt;/OBJECT&gt;</FIELD>
</OBJECT>
</OBJECT>
<OBJECT NAME="SubType111" ID="-1">
<FIELD NAME="TestID">-1</FIELD>
<FIELD NAME="Sequence">3</FIELD>
<FIELD NAME="ParentSequence">1</FIELD>
<FIELD NAME="ExtID">-1</FIELD>
<FIELD NAME="ExtName">GHI</FIELD>
</OBJECT>
</SUBTYPE>
<SUBTYPE NAME="SubType2"/>
<SUBTYPE NAME="SubType3"/>
</OBJECT>');
-- DDL and sample data population, end

;WITH rs AS
(
SELECT ID, TRY_CAST(xmlfield AS XML) AS cartxml
FROM @tbl
)
SELECT ID
, c.value('(FIELD[@NAME="TestID"]/text())[1]', 'INT') AS TestID
, c.value('(FIELD[@NAME="Sequence"]/text())[1]', 'INT') AS [Sequence]
, c.value('(FIELD[@NAME="ParentSequence"]/text())[1]', 'INT') AS 
ParentSequence
, c.value('(FIELD[@NAME="ExtID"]/text())[1]', 'INT') AS ExtID
, c.value('(FIELD[@NAME="ExtName"]/text())[1]', 'VARCHAR(20)') AS 
ExtName
,c1.value('(FIELD[@NAME="AttendeeID"]/text())[1]', 'VARCHAR(20)') AS 
AttendeeId,
,c1.value('(FIELD[@NAME="AttendeeID_Name"]/text())[1]', 
'VARCHAR(20)') AS AttendeeName,
FROM src As T
CROSS APPLY cartxml.nodes('/OBJECT/SUBTYPE/OBJECT[@ID="-1"]') as 
t2(c)
OUTER APPLY cartxml.nodes('/ 
OBJECT/SUBTYPE/OBJECT[@ID="-1"]/FIELD[@NAME="__ExtendedData"]') as 
t3(c1)

Answer 1

请尝试以下解决方案。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmlfield NVARCHAR(MAX));
INSERT INTO @tbl (xmlfield) VALUES
(N'<OBJECT CLASS="Test1" ID="-1" FULL="FULL" VERSION="1">
<SUBTYPE NAME="SubType1">
<OBJECT NAME="SubType111" ID="-1">
<FIELD NAME="TestID">-1</FIELD>
<FIELD NAME="Sequence">1</FIELD>
<FIELD NAME="ParentSequence">-1</FIELD>
<FIELD NAME="ExtID">-1</FIELD>
<FIELD NAME="ExtName">ABC</FIELD>
</OBJECT>
<OBJECT NAME="SubType111" ID="-1">
<FIELD NAME="TestID">-1</FIELD>
<FIELD NAME="Sequence">2</FIELD>
<FIELD NAME="ParentSequence">1</FIELD>
<FIELD NAME="ExtID">-1</FIELD>
<FIELD NAME="ExtName">DEF</FIELD>
<FIELD NAME="__ExtendedData">&lt;OBJECT 
CLASS="Meet123" ID="-1" FULL="FULL" 
VERSION="1"&gt;&lt;FIELD 
NAME="OrderDetailID"&gt;-1&lt;/FIELD&gt;&lt;FIELD 
NAME="OrderID"&gt;-1&lt;/FIELD&gt;&lt;FIELD 
NAME="Sequence"&gt;0&lt;/FIELD&gt;&lt;FIELD 
NAME="AttendeeID"&gt;123&lt;/FIELD&gt;&lt;FIELD NAME="AttendeeID_Name"&gt;Test, Mark/I H 6&lt;/FIELD&gt;&lt;FIELD 
NAME="ShowList"&gt;1&lt;/FIELD&gt;&lt;FIELD 
NAME="BdgeName"&gt;Mark&lt;/FIELD&gt;&lt;FIELD 
NAME="BadgeCompanyName"&gt;I H 6&lt;/FIELD&gt;
&lt;/OBJECT&gt;</FIELD>
</OBJECT>
<OBJECT NAME="SubType111" ID="-1">
<FIELD NAME="TestID">-1</FIELD>
<FIELD NAME="Sequence">3</FIELD>
<FIELD NAME="ParentSequence">1</FIELD>
<FIELD NAME="ExtID">-1</FIELD>
<FIELD NAME="ExtName">GHI</FIELD>
</OBJECT>
</SUBTYPE>
<SUBTYPE NAME="SubType2"/>
<SUBTYPE NAME="SubType3"/>
</OBJECT>');
-- DDL and sample data population, end
;WITH rs AS
(
SELECT ID, TRY_CAST(xmlfield AS XML) AS cartxml
FROM @tbl
)
SELECT ID
, c.value('(FIELD[@NAME="TestID"]/text())[1]', 'INT') AS TestID
, c.value('(FIELD[@NAME="Sequence"]/text())[1]', 'INT') AS [Sequence]
, c.value('(FIELD[@NAME="ParentSequence"]/text())[1]', 'INT') AS ParentSequence
, c.value('(FIELD[@NAME="ExtID"]/text())[1]', 'INT') AS ExtID
, c.value('(FIELD[@NAME="ExtName"]/text())[1]', 'VARCHAR(20)') AS ExtName
, w.value('(OBJECT/FIELD[@NAME="AttendeeID"]/text())[1]', 'VARCHAR(20)') AS AttendeeID
, w.value('(OBJECT/FIELD[@NAME="AttendeeID_Name"]/text())[1]', 'VARCHAR(20)') AS AttendeeID_Name
FROM rs AS t
CROSS APPLY cartxml.nodes('/OBJECT/SUBTYPE/OBJECT[@ID="-1"]') as t1(c)
CROSS APPLY (VALUES(TRY_CAST(c.query('FIELD[@NAME="__ExtendedData"]').value('.','NVARCHAR(MAX)') AS XML))) AS t2(w)
WHERE w.exist('/OBJECT[@CLASS="Meet123"]') = 1;

输出

父序列与会者ID名称<2><1>-1

ID	测试ID	序列	扩展名
1	-1