Início / coding / Perguntas / 79233605
Accepted
Dominique
Asked: 2024-11-28 19:08:35 +0800 CST

XPath complexo em colunas XML em uma tabela do SQL Server

  • 772

Tenho uma tabela, contendo uma coluna XML XmlMsg. Esta coluna pode conter orderstatus, vehiclestatus, ....

Estou interessado apenas em orderstatusnós, e mais exatamente nos dois casos seguintes:

  1. O último orderparté "Pendente".
  2. O último orderparté "Pendente" e o penúltimo é "Concluído".

Um exemplo do primeiro é o seguinte (mostrando apenas a parte relevante):

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

Um exemplo do segundo é o seguinte (também mostrando apenas a parte relevante):

<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>

Consegui obter a primeira lista com esta consulta:

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

No entanto, não tenho ideia de como obter a segunda lista.

Alguém tem alguma ideia?

sql

3 respostas

  1. Geralmente é mais fácil obter essas coisas usando o método nodes, algo como:

    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

    Então você pode usar algum LAG/LEAD para procurar os valores relevantes ou colocar os dados em uma tabela temporária e processá-los a partir daí.

    Saída:

    peça de encomenda tipo de evento eu ia bandeira
    3 Pendente 1 1
    2 Completo 2 1
    3 Pendente 2 1
    • 1

  2. Você pode usar o XPath 1.0 para ambas as consultas:

    1. Obtenha os orderstatusnós onde o último orderpartestá "Pending":

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

    2. Obtenha os orderstatusnós onde o último orderpartestá "Pending"e o penúltimo é "Complete":

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

    db<>fiddle (por @Charlieface)

    • 1
  3. Best Answer

    A última peça do pedido está "Pendente".

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

    A última parte do pedido é "Pendente" e a penúltima é "Concluído".

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

relate perguntas