时间戳

首页 > 解决方案 > 如何从xml文件返回数据

sql - 如何从xml文件返回数据

问题描述

如何从 product-lineitems 返回数据。我的意思是：数量，数量单位

我预计：

Product-Id  unit    quantity    tax-rate
0001        BOX     1.0         0.23
0002        PCS     1.0         0.23

我正在尝试这样：但它返回重复的行:(

WITH XMLNAMESPACES(DEFAULT N'www address')
SELECT o.value(N'(current-order-no/text())[1]',N'varchar(10)') AS OrderNo
     , n.value(N'(quantity/text())[1]',N'varchar(10)') AS Qty
     , u.value(N'@unit',N'varchar(10)') AS Unit
FROM 
    dbSupply.dbo.MyXmlTable t
CROSS APPLY 
    t.XMLData.nodes(N'/orders/order') A(o)
OUTER APPLY 
    A.o.nodes(N'product-lineitems/product-lineitem') B(n)
OUTER APPLY 
    A.o.nodes(N'product-lineitems/product-lineitem/quantity') C(u)

<orders xmlns="www address">
    <order>
        <order-date>2019-09-05</order-date>
        <created-by>storefront</created-by>
        <original-order-no>000001</original-order-no>
        <currency>USD</currency>
        <taxation>gross</taxation>
        <invoice-no>0099999</invoice-no>
        <product-lineitems>
            <product-lineitem>
                <product-id>0001</product-id>
                <quantity unit="BOX">1.0</quantity>
                <tax-rate>0.23</tax-rate>
            </product-lineitem>
            <product-lineitem>
                <product-id>0002</product-id>
                <quantity unit="PCS">1.0</quantity>
                <tax-rate>0.23</tax-rate>
            </product-lineitem>
        </product-lineitems>
    </order>
</orders>

标签： sqlsql-serverxmlxml-parsing

解决方案

第二个outer apply将简单地乘以行数（1 x 2 x 2 = 4）。您最多可以使用一个或两个应用运算符来执行此操作：

WITH XMLNAMESPACES(DEFAULT N'www address')
SELECT onode.value('(original-order-no)[1]', 'nvarchar(10)') AS OrderNo
     , inode.value('(quantity)[1]',          'nvarchar(10)') AS Qty
     , inode.value('(quantity/@unit)[1]',    'nvarchar(10)') AS Unit
FROM (
    SELECT CAST('<orders xmlns="www address">
    <order>
        <order-date>2019-09-05</order-date>
        <created-by>storefront</created-by>
        <original-order-no>000001</original-order-no>
        <currency>USD</currency>
        <taxation>gross</taxation>
        <invoice-no>0099999</invoice-no>
        <product-lineitems>
            <product-lineitem>
                <product-id>0001</product-id>
                <quantity unit="BOX">1.0</quantity>
                <tax-rate>0.23</tax-rate>
            </product-lineitem>
            <product-lineitem>
                <product-id>0002</product-id>
                <quantity unit="PCS">1.0</quantity>
                <tax-rate>0.23</tax-rate>
            </product-lineitem>
        </product-lineitems>
    </order>
    </orders>' AS XML)
) AS t(XMLData)
CROSS APPLY t.XMLData.nodes(N'/orders/order') onodes(onode)
CROSS APPLY onode.nodes(N'./product-lineitems/product-lineitem') inodes(inode)

推荐阅读