问题描述在SQL Server中解析XML数据

Question

我在SQL Server中解析一些发货细节(发送给我们的XML格式)有一些困难。

XML结构如下:

<Parent>
    <ShipmentNotice>
        <VendorSKU>ABC123</VendorSKU>
            <SerialNumbers>
                <string>AAAABBBB</string>
                <string>11112222</string>
                <string>CCCCDDDD</string>
                <string>33334444</string>
            </SerialNumbers>
        </VendorSKU>
    </ShipmentNotice>
    <ShipmentNotice>
        <VendorSKU>123ABC</VendorSKU>
            <SerialNumbers>
                <string>EEEEFFFF</string>
                <string>55556666</string>
                <string>GGGGHHHH</string>
                <string>77778888</string>
            </SerialNumbers>
        </VendorSKU>
    </ShipmentNotice>
</Parent>

本质上，发送的每个产品实例每个实例有两个<string>标签(AAAABBBB, 11112222)。我想做的是解析XML，并根据我提供的VendorSKU为每个<string>对创建一行。

我现在看到的是:

SELECT
   #ShippingRequests.[xmlcontent].value('(Parent/ShipmentNotice/VendorSKU)[1]', 'varchar(max)') AS VendorSKU,
   #ShippingRequests.[xmlcontent].value('(Parent/ShipmentNotice/SerialNumbers/string)[1]', 'varchar(max)') AS ICCID, 
    #ShippingRequests.[xmlcontent].value('(Parent/ShipmentNotice/SerialNumbers/string)[2]', 'varchar(max)') AS IMEI 
FROM
    (SELECT 
        StoreID, CAST(REPLACE(CAST([RequestData] as NVARCHAR(MAX)), '<User=/>', '') AS XML) AS xmlcontent 
     FROM 
        #ShippingRequests) #ShippingRequests
--CROSS APPLY #ShippingRequests.xmlcontent.nodes('(Parent/ShipmentNotice/SerialNumbers/string)') AS t(c)
WHERE 
    #ShippingRequests.xmlcontent.value('(Parent/ShipmentNotice/VendorSKU)[1]', 'varchar(max)') = 'ABC123'

在没有CROSS APPLY的情况下运行，会出现两个问题:

1. 我只得到第一个<string>对
2. 结果只显示如果VendorSKU ABC123是第一个<ShipmentNotice>标签的一部分。如果VendorSKU ABC123是下一个要解析的XML中的第二个<ShipmentNotice>，则没有任何显示。

用CROSS APPLY运行这个，我得到了大量的重复。

我怎么能得到这个查询运行的所有<string>对和运行，无论在哪里VendorSKU显示在XML?

Answer 1

这是一种可能的方法。在<ShipmentNotice>元素上分解XML数据，并对分解的XML应用VendorSKU检查，而不是对原始XML列，如下所示:

.......
CROSS APPLY #ShippingRequests.xmlcontent.nodes('(Parent/ShipmentNotice)') AS t(c)
WHERE c.value('(VendorSKU)[1]', 'varchar(max)') = '123ABC'

请注意，所发布的XML示例不是格式良好的，因为它有太多的VendorSKU结束标记。这是一个移除多余结束标签后的工作演示示例:

declare @ShippingRequests TABLE(StoreID int, RequestData XML)
insert into @ShippingRequests values(1, '<Parent>
    <ShipmentNotice>
        <VendorSKU>ABC123</VendorSKU>
            <SerialNumbers>
                <string>AAAABBBB</string>
                <string>11112222</string>
                <string>CCCCDDDD</string>
                <string>33334444</string>
            </SerialNumbers>
    </ShipmentNotice>
    <ShipmentNotice>
        <VendorSKU>123ABC</VendorSKU>
            <SerialNumbers>
                <string>EEEEFFFF</string>
                <string>55556666</string>
                <string>GGGGHHHH</string>
                <string>77778888</string>
            </SerialNumbers>
    </ShipmentNotice>
</Parent>')
SELECT
c.value('(VendorSKU)[1]', 'varchar(max)') AS VendorSKU,
c.value('(SerialNumbers/string)[1]', 'varchar(max)') AS ICCID, 
c.value('(SerialNumbers/string)[2]', 'varchar(max)') AS IMEI 
FROM
(
   SELECT 
   StoreID, CAST(REPLACE(CAST([RequestData] as NVARCHAR(MAX)), '<User=/>', '') AS XML) AS xmlcontent 
   FROM 
   @ShippingRequests
) 
#ShippingRequests
CROSS APPLY #ShippingRequests.xmlcontent.nodes('(Parent/ShipmentNotice)') AS t(c)
WHERE c.value('(VendorSKU)[1]', 'varchar(max)') = '123ABC' --'ABC123'

输出:

| VendorSKU |    ICCID |     IMEI |
|-----------|----------|----------|
|    123ABC | EEEEFFFF | 55556666 |