主页 / coding / 问题 / 79233605
Accepted
Dominique
Asked: 2024-11-28 19:08:35 +0800 CST

SQL Server 表中 XML 列中的复杂 XPath

  • 772

我有一张表，其中包含一个 XML 列XmlMsg。此列可以包含orderstatus、、vehiclestatus...

我只对orderstatus节点感兴趣，更确切地说对以下两种情况感兴趣：

  1. 最后一个orderpart是“待定”。
  2. 最后一个orderpart是“待定”，倒数第二个是“已完成”。

第一个例子如下（仅显示相关部分）：

<orderstatus responsecode="0"
             ...
             numberoforderparts="3">
    ...
    <orderparts>
        <orderpart orderpartnumber="1"/>
        <orderpart orderpartnumber="2"/>
        <orderpart orderpartnumber="3">
            <eventtype>Pending</eventtype>
            ...
        </orderpart>
    </orderparts>
</orderstatus>

第二个示例如下（也只显示相关部分）：

<orderstatus responsecode="0"
             ...
             numberoforderparts="3">
    ...
    <orderparts>
        <orderpart orderpartnumber="1"/>
        <orderpart orderpartnumber="2">
            <eventtype>Completed</eventtype>
            ...
        </orderpart>
        <orderpart orderpartnumber="3">
            <eventtype>Pending</eventtype>
            ...
        </orderpart>
    </orderparts>
</orderstatus>

我能够通过以下查询获取第一个列表：

SELECT *,
    XmlMsg.value('(/orderstatus/@numberoforderparts)[1]', 'INT') AS NumberOfOrderParts,
    
    -- Retrieve the last orderpart with eventtype="Pending"
    XmlMsg.query('(/orderstatus/orderparts/orderpart[@orderpartnumber = (/orderstatus/@numberoforderparts)[1] and eventtype="Pending"])[1]') AS LastOrderPart,
    
    -- Retrieve the second-to-last orderpart with eventtype="Completed"
    XmlMsg.query('(/orderstatus/orderparts/orderpart[@orderpartnumber = (/orderstatus/@numberoforderparts)[1] - 1 and eventtype="Completed"])[1]') AS PreviousOrderPart
FROM 
    [dbo].[AWIMessageLogs]
WHERE 
    Source = 'RCV_RESP' 
    AND LogDateTime >= '2024-11-26' 
    AND LogDateTime <= '2024-11-26 15:00' 
    AND XmlMsg.exist('/orderstatus') = 1
    -- Ensure that the last orderpart has eventtype="Pending"
    AND XmlMsg.query('(/orderstatus/orderparts/orderpart[@orderpartnumber = (/orderstatus/@numberoforderparts)[1] and eventtype="Pending"])[1]') IS NOT NULL

但是，我不知道如何获取第二个列表。

有人有想法吗？

sql

3 个回答

  1. 通常使用节点方法更容易获得这些内容，例如：

    CREATE TABLE #xml (x xml, id int IDENTITY)

INSERT INTO #xml VALUES(N'<orderstatus responsecode="0"
             numberoforderparts="3">
    
    <orderparts>
        <orderpart orderpartnumber="1"/>
        <orderpart orderpartnumber="2"/>
        <orderpart orderpartnumber="3">
            <eventtype>Pending</eventtype>
        </orderpart>
    </orderparts>
</orderstatus>'), ('<orderstatus responsecode="0"
             numberoforderparts="3">
    <orderparts>
        <orderpart orderpartnumber="1"/>
        <orderpart orderpartnumber="2">
            <eventtype>Complete</eventtype>
        </orderpart>
        <orderpart orderpartnumber="3">
            <eventtype>Pending</eventtype>
        </orderpart>
    </orderparts>
</orderstatus>')

SELECT  *
FROM    (
    SELECT  *
    ,   CASE 
            WHEN orderpart = max(orderpart) OVER(partition BY id) AND eventtype = 'Pending' -- Last pending
            OR (
                orderpart = max(orderpart) OVER(partition BY id) -1 AND eventtype = 'Complete' -- Complete and last is pending
                AND LEAD(eventtype) OVER(PARTITION BY ID ORDER BY orderpart) = 'Pending'
            )
        THEN 1 ELSE 0 END AS flag
    
    FROM    (
        SELECT  op.value('@orderpartnumber', 'int') AS orderpart, et.value('text()[1]', 'nvarchar(100)') AS eventtype
        ,   id
        FROM    #xml t
        CROSS apply x.nodes(N'orderstatus/orderparts/orderpart') t1(op)
        OUTER apply op.nodes(N'eventtype') t2(et)
        ) x
    ) x
WHERE   flag = 1

    然后，您可以使用一些 LAG/LEAD 来查找相关值或将数据放入临时表中并从那里进行处理。

    输出：

    订单部分 事件类型 ID 旗帜
    3 待办的 1 1
    2 完全的 2 1
    3 待办的 2 1
    • 1

  2. 您可以对这两个查询使用 XPath 1.0：

    1. 获取orderstatus最后一个节点的orderpart位置"Pending"：

      //orderstatus[
    orderparts/orderpart[last()]/eventtype = "Pending"
]

    2. 获取orderstatus最后一个节点orderpart和"Pending"倒数第二个节点"Complete"：

      //orderstatus[
    orderparts[
        orderpart[last()]/eventtype = "Pending" and
        orderpart[last()-1]/eventtype = "Complete"
    ]
]

    db<>fiddle (作者@Charlieface)

    • 1
  3. Best Answer

    最后一个订单部分是“待处理”。

    orderstatus[orderparts/orderpart[last()]/eventtype='Pending']

    最后一个订单部分是“待定”，倒数第二个订单部分是“完成”。

    orderstatus[orderparts/orderpart[last()]/eventtype='Pending' and
            orderparts/orderpart[last()-1]/eventtype='Completed']
    • 1

相关问题