sql - 读取表中的 XML

Question

我有一个将 XML 作为参数传递的存储过程，然后我必须读取该 XML 并进行一些更新。 XML的格式是

<Requests>
    <RequestReference>R12345</RequestReference>
    <Inventory InventoryId="1" InventoryName="I1">
        <RequestCodes>
            <Code>AAA</Code>
        </RequestCodes>
    </Inventory>
    <Inventory InventoryId="2" InventoryName="I2">
        <RequestCodes>
            <Code>BBB</Code>
            <Code>CCC</Code>
        </RequestCodes>
    </Inventory>
    <Inventory InventoryId="3" InventoryName="I3">
        <RequestCodes>
            <Code>DDD</Code>
            <Code>EEE</Code>
            <Code>FFF</Code>
        </RequestCodes>
    </Inventory>
</Requests>

现在我需要在表中读取 XML

RequestReference    InventoryId InventoryName           Code
R12345                    1           I1            AAA
R12345                    2           I2            BBB
R12345                    2           I2            CCC
R12345                    3           I3            DDD
R12345                    3           I3            EEE
R12345                    3           I3            FFF

我正在尝试类似的东西

SELECT 
    T.Item.value('@InventoryId', 'VARCHAR(3)')  AS InventoryId,
    T.Item.value('@InventoryName', 'VARCHAR(3)')    AS InventoryName,
    T.Item.value('RequestCodes[1]/Code[1]', 'VARCHAR(5)')   AS Code
FROM   
   @xmlDoc.nodes('Requests/Inventory') 
   AS T(Item)

它产生的结果是

InventoryId InventoryName   Code
1                 I1    AAA
2                 I2    BBB
3                 I3    DDD

所以这似乎有效，但没有完全产生预期的结果。

Answer 1

利用:

select *
from
(
SELECT
    T.Item.value('../../../RequestReference[1]', 'VARCHAR(20)')  AS RequestReference,
    T.Item.value('../../@InventoryId', 'VARCHAR(3)')  AS InventoryId,
    T.Item.value('../../@InventoryName', 'VARCHAR(3)')    AS InventoryName,
    T.Item.value('.', 'VARCHAR(5)')   AS Code
FROM   
   @xmlDoc.nodes('//Code') 
   AS T(Item)
)t
order by InventoryId, InventoryName, Code