在TSQL中決議、過濾嵌套的XML

我有一個表 (table1)，其中有一列包含 XML 資料。我需要決議該 XML 并從元素的子元素創建資料行 - 輸出需要類似于 TestID Sequence ParentSequence ExtID ExtName -1 1 -1 1 ABC -1 2 -1 1 DEF -1 2 -1 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)

uj5u.com熱心網友回復：

請嘗試以下解決方案。

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;

輸出

標籤：sql服务器xmltsql查询

上一篇：節點的XML計數為0C#

下一篇：從無序列的優先串列中選擇第一個孩子