Синтаксический анализ Xml в t-sql, проблема с использованием селектора XQuery с нескольких уровней внутри корня и анализ значений из выбранных узлов

ProgramBox

Синтаксический анализ Xml в t-sql, проблема с использованием селектора XQuery с нескольких уровней внутри корня и анализ значений из выбранных узлов

Post author:admin
Запись опубликована:19 марта, 2023
Post category:Вопросы по программированию

#sql-server #xml #xpath #soap #xquery-sql

Вопрос:

Я очень новичок в синтаксическом анализе значений в Xml и надеялся, что кто-нибудь с некоторыми знаниями Xml (XQUERY / XPATH) объяснит мне, как анализировать определенные значения из следующего Xml с помощью t-sql. Я включу попытки кода, которые я предпринял, чтобы показать, что я не просто прошу кого-то сделать это за меня.

Учитывая следующий ответ Soap Xml:

 
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:aah:remtp:schemas:PlaceOrderResponse:1.00">
       <SOAP-ENV:Body>
          <PlaceOrderResponse xmlns="urn:aah:remtp:schemas:PlaceOrderResponse:1.00">
             <StatusCode>0</StatusCode>
             <TextReply>Successful</TextReply>
             <PlaceOrderReplyMessage>
                <TransmissionId>aac1e254-bc05-4d69-b04d-31d5fead314e</TransmissionId>
                <ReplyOrder>
                   <TrackingId>aac1e254-bc05-4d69-b04d-31d5fead314e</TrackingId>
                   <CustomerAccount>A_TEST</CustomerAccount>
                   <CustomerOrder>aac1e254-bc05-4d69-b04d-31d5fead314e</CustomerOrder>
                   <ReplyGroup>
                      <LineNumber>1</LineNumber>
                      <ReplyItem>
                         <ReplyCode>0</ReplyCode>
                         <ReplyData>10</ReplyData>
                         <ReplyMsg>Successful</ReplyMsg>
                      </ReplyItem>
                   </ReplyGroup>
                   <ReplyGroup>
                      <LineNumber>2</LineNumber>
                      <ReplyItem>
                         <ReplyCode>0</ReplyCode>
                         <ReplyData>15</ReplyData>
                         <ReplyMsg>Successful</ReplyMsg>
                      </ReplyItem>
                   </ReplyGroup>
                </ReplyOrder>
             </PlaceOrderReplyMessage>
          </PlaceOrderResponse>
       </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

И проанализируйте ReplyGroups в табличную переменную (как только я отсортирую выбор, это должно быть легко — я думаю).

Я попробовал следующее:

 
    DECLARE @XmlDocument XML
    DECLARE @StatusCode VARCHAR(MAX)
    DECLARE @TextReply VARCHAR(MAX)
    DECLARE @CustomerOrder VARCHAR(MAX)
    
    SELECT @XmlDocument = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:aah:remtp:schemas:PlaceOrderResponse:1.00">
       <SOAP-ENV:Body>
          <PlaceOrderResponse xmlns="urn:aah:remtp:schemas:PlaceOrderResponse:1.00">
             <StatusCode>0</StatusCode>
             <TextReply>Successful</TextReply>
             <PlaceOrderReplyMessage>
                <TransmissionId>aac1e254-bc05-4d69-b04d-31d5fead314e</TransmissionId>
                <ReplyOrder>
                   <TrackingId>aac1e254-bc05-4d69-b04d-31d5fead314e</TrackingId>
                   <CustomerAccount>A_TEST</CustomerAccount>
                   <CustomerOrder>aac1e254-bc05-4d69-b04d-31d5fead314e</CustomerOrder>
                   <ReplyGroup>
                      <LineNumber>1</LineNumber>
                      <ReplyItem>
                         <ReplyCode>0</ReplyCode>
                         <ReplyData>10</ReplyData>
                         <ReplyMsg>Successful</ReplyMsg>
                      </ReplyItem>
                   </ReplyGroup>
                   <ReplyGroup>
                      <LineNumber>2</LineNumber>
                      <ReplyItem>
                         <ReplyCode>0</ReplyCode>
                         <ReplyData>15</ReplyData>
                         <ReplyMsg>Successful</ReplyMsg>
                      </ReplyItem>
                   </ReplyGroup>
                </ReplyOrder>
             </PlaceOrderReplyMessage>
          </PlaceOrderResponse>
       </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>'
    
            --  PlaceOrderResponse/StatusCode
    SELECT @StatusCode = CONVERT(VARCHAR(MAX), @XmlDocument.query('/PlaceOrderResponse/StatusCode'))
    
            --  PlaceOrderResponse/TextReply
    SELECT @TextReply = CONVERT(VARCHAR(MAX), @XmlDocument.query('/PlaceOrderResponse/TextReply'))
    
            --  PlaceOrderResponse/PlaceOrderReplyMessage/ReplyOrder/CustomerOrder
    SELECT @CustomerOrder = CONVERT(VARCHAR(MAX), @XmlDocument.query('/PlaceOrderResponse/PlaceOrderReplyMessage/ReplyOrder'))
    
    
    
    --SELECT @StatusCode As StatusCode, @TextReply As TextReply, @CustomerOrder As CustomerOrder
    --INSERT INTO @ResponseLines(LineNumber, ReplyCode, ReplyData, ReplyMessage)
    SELECT @XmlDocument.query('/PlaceOrderResponse/PlaceOrderReplyMessage/ReplyOrder/ReplyGroup')

К сожалению, после этого все переменные пусты, и я думаю, это потому, что я не начинаю с корневого узла в своих селекторах. Кроме того, если я удалю два узла soap envelope и любые пространства имен, значения будут заполнены, но фактическим узлом, а не значением внутри каждого узла.

Я был бы признателен, если бы кто-нибудь мог мне показать:

Как начать запрашивать Xml, начиная с <PlaceOrderResponse> узла, а не с корня, чтобы получить правильные узлы
Как анализировать значения внутри узлов, на которые я смотрю (StatusCode, ReplyText, CustomerOrder) .
Как выбрать несколько <ReplyGroup> элементов в структуру таблицы. Пожалуйста, обратите <LineNumber> внимание, что элемент находится выше по иерархии дерева Xml, чем <ReplyItem> связанный с ним.

Ответ №1:

Вам нужно позаботиться о пространствах имен. Посмотрите, как это сделать ниже.

SQL

 DECLARE @XmlDocument XML
DECLARE @StatusCode VARCHAR(MAX)
DECLARE @TextReply VARCHAR(MAX)
DECLARE @CustomerOrder VARCHAR(MAX)
    
SELECT @XmlDocument = 
N'<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
                   xmlns:ns1="urn:aah:remtp:schemas:PlaceOrderResponse:1.00">
    <SOAP-ENV:Body>
        <PlaceOrderResponse xmlns="urn:aah:remtp:schemas:PlaceOrderResponse:1.00">
            <StatusCode>0</StatusCode>
            <TextReply>Successful</TextReply>
            <PlaceOrderReplyMessage>
                <TransmissionId>aac1e254-bc05-4d69-b04d-31d5fead314e</TransmissionId>
                <ReplyOrder>
                    <TrackingId>aac1e254-bc05-4d69-b04d-31d5fead314e</TrackingId>
                    <CustomerAccount>A_TEST</CustomerAccount>
                    <CustomerOrder>aac1e254-bc05-4d69-b04d-31d5fead314e</CustomerOrder>
                    <ReplyGroup>
                        <LineNumber>1</LineNumber>
                        <ReplyItem>
                            <ReplyCode>0</ReplyCode>
                            <ReplyData>10</ReplyData>
                            <ReplyMsg>Successful</ReplyMsg>
                        </ReplyItem>
                    </ReplyGroup>
                </ReplyOrder>
            </PlaceOrderReplyMessage>
        </PlaceOrderResponse>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';

;WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS [SOAP-ENV]
    ,'urn:aah:remtp:schemas:PlaceOrderResponse:1.00' AS ns2)
SELECT @StatusCode = c.value('(ns2:StatusCode/text())[1]','INT') 
    , @TextReply = c.value('(ns2:TextReply/text())[1]','VARCHAR(100)') 
    , @CustomerOrder = c.value('(ns2:PlaceOrderReplyMessage/ns2:ReplyOrder/ns2:CustomerOrder/text())[1]','VARCHAR(100)') 
FROM @XmlDocument.nodes('/SOAP-ENV:Envelope/SOAP-ENV:Body/ns2:PlaceOrderResponse') AS t(c);

-- test
SELECT @StatusCode AS StatusCode
    , @TextReply AS TextReply
    , @CustomerOrder AS CustomerOrder;

Вывод

  ------------ ------------ -------------------------------------- 
| StatusCode | TextReply  |            CustomerOrder             |
 ------------ ------------ -------------------------------------- 
|          0 | Successful | aac1e254-bc05-4d69-b04d-31d5fead314e |
 ------------ ------------ --------------------------------------

SQL # 2

 ;WITH XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS [SOAP-ENV]
    , DEFAULT 'urn:aah:remtp:schemas:PlaceOrderResponse:1.00')
--INSERT INTO @ResponseLines(LineNumber, ReplyCode, ReplyData, ReplyMessage)
SELECT c.value('(LineNumber/text())[1]','INT') 
    , c.value('(ReplyItem/ReplyCode/text())[1]','VARCHAR(100)') 
    , c.value('(ReplyItem/ReplyData/text())[1]','VARCHAR(100)') 
    , c.value('(ReplyItem/ReplyMsg/text())[1]','VARCHAR(100)') 
FROM @XmlDocument.nodes('/SOAP-ENV:Envelope/SOAP-ENV:Body/PlaceOrderResponse/PlaceOrderReplyMessage/ReplyOrder/ReplyGroup') AS t(c);

1. Это фантастика @Ицхак Хабинский, мне просто нужно придумать, как расширить ваш ответ, чтобы получить узлы ReplyGroup (к сожалению, вопрос и фрагмент Xml, которые я опубликовал, показывают только один, но их может быть несколько). Но спасибо за очень быстрый ответ на мой вопрос.

2. Я обновил ответ с помощью возможного INSERT утверждения. Проверьте это.

3. @Yitshak — Ты потрясающий … спасибо … Надеюсь, в дальнейшем я смогу использовать эти примеры в других XML-ответах, которые я получаю, — и я уверен, что их будет много