Wählen Sie Werte aus dem XML-Feld in SQL Server 2008 aus

Berücksichtigen Sie, dass XML-Daten aus einer Tabelle 'table' stammen und in einer Spalte 'field' gespeichert sind: Verwenden Sie die XML-Methoden , extrahieren Sie Werte mit xml.value() , projektiere Knoten mit xml.nodes() , benutze CROSS APPLY um:

SELECT 
    p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,
    p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName
FROM table 
    CROSS APPLY field.nodes('/person') t(p)

Sie können nodes() und cross apply Weglassen, wenn jedes Feld genau ein Element 'person' enthält. Wenn es sich bei der XML-Datei um eine Variable handelt, wählen Sie FROM @variable.nodes(...) aus und benötigen den cross apply Nicht.

Dieser Beitrag war hilfreich, um mein Problem zu lösen, das ein etwas anderes XML-Format aufweist. Mein XML enthält eine Liste von Schlüsseln wie das folgende Beispiel und ich speichere das XML in der SourceKeys-Spalte in einer Tabelle mit dem Namen DeleteBatch:

<k>1</k>
<k>2</k>
<k>3</k>

Erstellen Sie die Tabelle und füllen Sie sie mit einigen Daten:

CREATE TABLE dbo.DeleteBatch (
    ExecutionKey INT PRIMARY KEY,
    SourceKeys XML)

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 1, 
    (CAST('<k>1</k><k>2</k><k>3</k>' AS XML))

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 2, 
    (CAST('<k>100</k><k>101</k>' AS XML))

Hier ist mein SQL, um die Schlüssel aus dem XML auszuwählen:

SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
    CROSS APPLY SourceKeys.nodes('/k') t(p)

Hier sind die Abfrageergebnisse ...

 ExecutionKey Key 
 1 1 
 1 2 
 1 3 
 2 100 
 2 101 

Dies könnte Ihre Frage beantworten:

select cast(xmlField as xml) xmlField into tmp from (
select '<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>' xmlField
union select '<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>'
union select '<person><firstName>Bob</firstName><lastName>Burns</lastName></person>'
) tb

SELECT
    xmlField.value('(person/firstName)[1]', 'nvarchar(max)') as FirstName
    ,xmlField.value('(person/lastName)[1]', 'nvarchar(max)') as LastName
FROM tmp

drop table tmp

Blimey. Dies war ein wirklich nützlicher Thread zu entdecken.

Ich fand einige dieser Vorschläge immer noch verwirrend. Wann immer ich value mit [1] in der Zeichenfolge würde nur der erste Wert abgerufen. Und einige Vorschläge empfohlen mit cross apply was (in meinen Tests) einfach viel zu viele Daten zurückgebracht hat.

Hier ist mein einfaches Beispiel, wie Sie ein xml -Objekt erstellen und dann seine Werte in eine Tabelle auslesen.

DECLARE @str nvarchar(2000)

SET @str = ''
SET @str = @str + '<users>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mike</firstName>'
SET @str = @str + '     <lastName>Gledhill</lastName>'
SET @str = @str + '     <age>31</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mark</firstName>'
SET @str = @str + '     <lastName>Stevens</lastName>'
SET @str = @str + '     <age>42</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Sarah</firstName>'
SET @str = @str + '     <lastName>Brown</lastName>'
SET @str = @str + '     <age>23</age>'
SET @str = @str + '  </user>'
SET @str = @str + '</users>'

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) 

--  Iterate through each of the "users\user" records in our XML
SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName',
    x.Rec.query('./age').value('.', 'int') AS 'Age'
FROM @xml.nodes('/users/user') as x(Rec)

Und hier ist die Ausgabe:

Es ist eine bizarre Syntax, aber mit einem anständigen Beispiel ist es einfach genug, Ihre eigenen SQL Server-Funktionen zu erweitern.

Apropos, hier ist die richtige Antwort auf diese Frage.

Angenommen, Sie haben Ihre XML-Daten in einem @xml Variable vom Typ xml (wie in meinem obigen Beispiel gezeigt). So würden Sie die drei Datenzeilen aus der in der Frage angegebenen XML-Datei zurückgeben:

SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName'
FROM @xml.nodes('/person') as x(Rec)

Wenn Sie in der Lage sind, Ihre XML-Datei in ein Stammelement einzubinden, lautet Ihre Lösung beispielsweise wie folgt:

DECLARE @PersonsXml XML = '<persons><person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person></persons>'

SELECT  b.value('(./firstName/text())[1]','nvarchar(max)') as FirstName, b.value('(./lastName/text())[1]','nvarchar(max)') as LastName
FROM @PersonsXml.nodes('/persons/person') AS a(b)

SELECT 
cast(xmlField as xml).value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
cast(xmlField as xml).value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]

MSSQL verwendet reguläre XPath-Regeln wie folgt:

• knotenname Wählt alle Knoten mit dem Namen "Knotenname" aus.
• / Wählt vom Wurzelknoten aus
• // Wählt Knoten im Dokument aus dem aktuellen Knoten aus, die der Auswahl entsprechen, unabhängig davon, wo sie sich befinden
• . Wählt den aktuellen Knoten aus
• .. Wählt das übergeordnete Element des aktuellen Knotens aus
• @ Wählt Attribute aus

W3Schools

/ * In diesem Beispiel wird eine XML-Variable mit einem Schema verwendet * /

IF EXISTS (SELECT * FROM sys.xml_schema_collections 
           WHERE name = 'OrderingAfternoonTea')
BEGIN
    DROP XML SCHEMA COLLECTION dbo.OrderingAfternoonTea 
END
GO

CREATE XML SCHEMA COLLECTION dbo.OrderingAfternoonTea AS
N'<?xml version="1.0" encoding="UTF-16" ?>
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     targetNamespace="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     xmlns="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     elementFormDefault="qualified"
     version="0.10"
   > 
    <xsd:complexType name="AfternoonTeaOrderType">
       <xsd:sequence>
         <xsd:element name="potsOfTea" type="xsd:int"/>
         <xsd:element name="cakes" type="xsd:int"/>
         <xsd:element name="fruitedSconesWithCream" type="xsd:int"/>
         <xsd:element name="jams" type="xsd:string"/>
      </xsd:sequence>
      <xsd:attribute name="schemaVersion" type="xsd:long" use="required"/>
    </xsd:complexType>

    <xsd:element name="afternoonTeaOrder"
                 type="TFor2:AfternoonTeaOrderType"/>

  </xsd:schema>' ;
GO

DECLARE @potsOfTea int;
DECLARE @cakes int;
DECLARE @fruitedSconesWithCream int;
DECLARE @jams nvarchar(128);

DECLARE @RequestMsg NVARCHAR(2048);
DECLARE @RequestXml XML(dbo.OrderingAfternoonTea);

set @potsOfTea = 5;
set @cakes = 7;
set @fruitedSconesWithCream = 25;
set @jams = N'medlar jelly, quince and mulberry';

SELECT @RequestMsg = N'<?xml version="1.0" encoding="utf-16" ?>
<TFor2:afternoonTeaOrder schemaVersion="10"
    xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea">
    <TFor2:potsOfTea>' + CAST(@potsOfTea as NVARCHAR(20)) 
        + '</TFor2:potsOfTea>
    <TFor2:cakes>' + CAST(@cakes as NVARCHAR(20)) + '</TFor2:cakes>
    <TFor2:fruitedSconesWithCream>' 
        + CAST(@fruitedSconesWithCream as NVARCHAR(20))
        + '</TFor2:fruitedSconesWithCream>
    <TFor2:jams>' + @jams + '</TFor2:jams>
</TFor2:afternoonTeaOrder>';

SELECT @RequestXml  = CAST(CAST(@RequestMsg AS VARBINARY(MAX)) AS XML) ;

with xmlnamespaces('http://Tfor2.com/schemas/actions/orderAfternoonTea'
                    as tea)
select
    cast( x.Rec.value('.[1]/@schemaVersion','nvarchar(20)') as bigint )
        as schemaVersion,
    cast( x.Rec.query('./tea:potsOfTea')
               .value('.','nvarchar(20)') as bigint ) as potsOfTea,
    cast( x.Rec.query('./tea:cakes')
               .value('.','nvarchar(20)') as bigint )  as cakes,
    cast( x.Rec.query('./tea:fruitedSconesWithCream')
               .value('.','nvarchar(20)') as bigint ) 
      as fruitedSconesWithCream,
    x.Rec.query('./tea:jams').value('.','nvarchar(50)')  as jams
from @RequestXml.nodes('/tea:afternoonTeaOrder')  as x(Rec);

select @RequestXml.query('/*')