[FIXED] Analysieren, filtern Sie verschachteltes XML in TSQL

Ausgabe

Ich habe eine Tabelle (Tabelle1), die eine Spalte mit XML-Daten enthält. Ich muss dieses XML analysieren und Datenzeilen aus den untergeordneten Elementen des Elements erstellen. Die Ausgabe muss so etwas wie TestID Sequence ParentSequence ExtID ExtName -1 1 -1 1 ABC -1 2 -1 1 DEF -1 2 -1 sein 1 WHI

Aber bei jeder anderen Methode, die ich ausprobiert habe, erhalte ich eine leere Ergebnismenge.

Ich habe mich auf den Zugriff auf Sequence konzentriert, da der Rest dem gleichen Prozess folgt. Nicht sicher, warum dies nicht funktioniert. Jede Hilfe in dieser Hinsicht wird geschätzt. Vielen Dank. Das SQL, das ich ausprobiert habe, ist nach dem XML (kommentierter Text sind die Optionen, die ich ausprobiert habe)

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)

Lösung

Bitte versuchen Sie die folgende Lösung.

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;

Ausgabe

Beantwortet von – Yitzhak Khabinsky

Antwort geprüft von – Katrina (FixError Volunteer)