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"><OBJECT
CLASS="Meet123" ID="-1" FULL="FULL"
VERSION="1"><FIELD
NAME="OrderDetailID">-1</FIELD><FIELD
NAME="OrderID">-1</FIELD><FIELD
NAME="Sequence">0</FIELD><FIELD
NAME="AttendeeID">123</FIELD><FIELD NAME="
AttendeeID _Name">Test, Mark/I H 6</FIELD><FIELD
NAME="ShowList">1</FIELD><FIELD
NAME="BdgeName">Mark</FIELD><FIELD
NAME="BadgeCompanyName">I H 6</FIELD>
</OBJECT></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"><OBJECT
CLASS="Meet123" ID="-1" FULL="FULL"
VERSION="1"><FIELD
NAME="OrderDetailID">-1</FIELD><FIELD
NAME="OrderID">-1</FIELD><FIELD
NAME="Sequence">0</FIELD><FIELD
NAME="AttendeeID">123</FIELD><FIELD NAME="AttendeeID_Name">Test, Mark/I H 6</FIELD><FIELD
NAME="ShowList">1</FIELD><FIELD
NAME="BdgeName">Mark</FIELD><FIELD
NAME="BadgeCompanyName">I H 6</FIELD>
</OBJECT></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
ICH WÜRDE | TestID | Reihenfolge | ParentSequence | ExtID | ExtName | Teilnehmer-ID | Teilnehmer-ID_Name |
---|---|---|---|---|---|---|---|
1 | -1 | 2 | 1 | -1 | DEF | 123 | Test, Mark/IH 6 |
Beantwortet von – Yitzhak Khabinsky
Antwort geprüft von – Katrina (FixError Volunteer)