I have a table (table1) which has a column containing XML data. I need to parse that XML and create rows of data from the child elements of the element - The output needs to be something like TestID Sequence ParentSequence ExtID ExtName -1 1 -1 1 ABC -1 2 -1 1 DEF -1 2 -1 1 GHI

But I am getting an empty result set with every other method I tried.
I have focused on accessing Sequence as rest will follow the same process. Not sure why this does not work. Any help in this regard is appreciated. Thank you. The SQL I have tried is after the XML(commented text is the options I have tried)

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)

CodePudding user response：

Please try the following solution.

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;

Output