問題描述

我有一些 XML 需要使用 SQL Server 2008 進(jìn)行解析.我想我已經(jīng)接近得到我想要的東西了，但是我沒有正確的語法(我相信).

I have some XML that I need to parse using SQL Server 2008. I think I'm close to getting what I want, but I don't have the correct syntax (I believe).

我有以下幾點:

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)

我得到以下結(jié)果:

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

我正在嘗試獲得以下信息:

I'm trying to get the following:

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

我做錯了什么?

推薦答案

抓取元素的語法是:

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)

這似乎也可以在沒有明確括號的情況下工作:

This also appears to work without the explicit parenthesis:

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

<小時>

@ 語法用于屬性，而不是 XQuery 中的元素.如果你有

The @ syntax is for attributes, not elements in XQuery. If you had

<InvoiceDetail title="something">

然后你就可以使用:

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

這是一篇關(guān)于使用 XQuery 值的好文章

這篇關(guān)于在 SQL Server 中選擇 XML 元素的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！