Wählen Sie XML-element in SQL Server

Habe ich einige XML, die ich brauche, um zu analysieren, mithilfe von SQL Server 2008. Ich glaube, ich bin in der Nähe zu bekommen, was ich will, aber ich habe nicht die richtige syntax (glaube ich).

Habe ich Folgendes:

DECLARE @doc XML
SET @doc = '<ROOT>          
    <InvoiceDetail>
        <OrderId>1000000</OrderId>
        <OrderTypeId>2</OrderTypeId>
        <Id>2000</Id>
        <InvoiceItems>
            <InvoiceItem>
                <LineId>1</LineId>
                <Cd>123456</Cd>
                <Description>Item 1</Description>
                <Quantity>1</Quantity>
                <UnitPrice>99.990000</UnitPrice>
            </InvoiceItem>
            <InvoiceItem>
                <LineId>2</LineId>
                <Cd>234567</Cd>
                <Description>Item 2</Description>
                <Quantity>1</Quantity>
                <UnitPrice>89.990000</UnitPrice>
            </InvoiceItem>
        </InvoiceItems>
    </InvoiceDetail>
    <InvoiceDetail>
        <OrderId>1200000</OrderId>
        <OrderTypeId>1</OrderTypeId>
        <Id>3000</Id>
        <InvoiceItems>
            <InvoiceItem>
                <LineId>1</LineId>
                <Cd>234567</Cd>
                <Description>Item 2</Description>
                <Quantity>1</Quantity>
                <UnitPrice>89.990000</UnitPrice>
            </InvoiceItem>
            <InvoiceItem>
                <LineId>2</LineId>
                <Cd>345678</Cd>
                <Description>Item 3</Description>
                <Quantity>1</Quantity>
                <UnitPrice>79.990000</UnitPrice>
            </InvoiceItem>
        </InvoiceItems>
    </InvoiceDetail>
</ROOT>'

SELECT 
      Invoices.Node.value('@OrderId', 'VARCHAR(10)') 'OrderID'
    , Invoices.Node.value('@Id', 'INT') 'InvoiceId'
    , Items.Cd.value('.', 'VARCHAR(14)') 'ItemId'
FROM 
    @doc.nodes('//InvoiceDetail') Invoices(Node)
    CROSS APPLY Invoices.Node.nodes('./InvoiceItems/InvoiceItem/Cd') Items(Cd)

Bekomme ich folgende Ergebnisse:

NULL    NULL    123456
NULL    NULL    234567
NULL    NULL    234567
NULL    NULL    345678

Ich versuche, das folgende:

1000000 2000    123456
1000000 2000    234567
1200000 3000    234567
1200000 3000    345678

Was mache ich falsch?

  • +1 für leicht überprüfbar/reproduzierbar Beispiel
InformationsquelleAutor jared | 2012-10-18
  1. 6

    Die Syntax fürs kopieren ein element:

    SELECT Invoices.Node.value('(OrderId)[1]', 'VARCHAR(10)') 'OrderID'
    , Invoices.Node.value('(Id)[1]', 'INT') 'InvoiceId'
    , Items.Cd.value('.', 'VARCHAR(14)') 'ItemId'
FROM 
    @doc.nodes('//InvoiceDetail') Invoices(Node)
    CROSS APPLY Invoices.Node.nodes('./InvoiceItems/InvoiceItem/Cd') Items(Cd)

    Dies scheint auch zu arbeiten, ohne explizit Klammern:

    Invoices.Node.value('OrderId[1]', 'VARCHAR(10)')

    Den @ syntax für Attribute, keine Elemente, die in der XQuery. Wenn Sie hatte

    <InvoiceDetail title="something">

    Dann wären Sie in der Lage, Abfrage, Verwendung:

    SELECT Invoices.Node.value('@title', 'VARCHAR(MAX)') AS Title
FROM @doc.nodes('//InvoiceDetail') Invoices(Node)

    Hier ist ein guter Artikel über die Verwendung von XQuery-Wert

    • Danke Adam für die Führung und den externen link.
    InformationsquelleAutor Adam Wenger
  2. 0

    Den folgenden SQL würde auch gut funktionieren mit Ihren Daten:

    --  Iterate through each of the "ROOT\InvoiceDetail" records in our XML
SELECT 
    x.Rec.query('./OrderId').value('.', 'nvarchar(2000)') AS 'OrderID',
    x.Rec.query('./Id').value('.', 'nvarchar(2000)') AS 'ID',
    items.cd.query('./Cd').value('.', 'nvarchar(2000)') AS 'ItemID'
FROM @doc.nodes('/ROOT/InvoiceDetail') as x(Rec)
CROSS APPLY x.Rec.nodes('./InvoiceItems/InvoiceItem') Items(Cd)

    Dies gibt uns diese Ergebnisse:

    Wählen Sie XML-element in SQL Server

    (Die syntax ist verwirrend, obwohl, ist es nicht !)

    InformationsquelleAutor Mike Gledhill
Schreibe einen Kommentar