如何解析SOAP XML在SQL Server中，并显示为表

Question

我需要解析器SQL Server中的SOAP XML并将其转换为表

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
    <soap:Body> 
    <ExecCommandResponse xmlns="http://tempuri.org/"> 
     <ExecCommandResult> 
     <Result xmlns=""> 
      <row> 
      <LOT>VERL5B3002PL</LOT> 
      <ID>115</ID> 
      <WH>710</WH> 
      <STPL>12</STPL> 
      </row> 
      <row> 
      <LOT>VERL68804EVN</LOT> 
      <ID>3716</ID> 
      <WH>771</WH> 
      <STPL>6</STPL> 
      </row> 
     </Result> 
     </ExecCommandResult> 
    </ExecCommandResponse> 
    </soap:Body> 
</soap:Envelope> 

我需要解析器SQL Server中的SOAP XML并将其转换到表

LOT   | ID | WH | STPL 
VERL68804EVN | 3716 | 771 | 6 

Answer 1

使用了最新功能来查询XML。

您的XML在命名空间上看起来不太干净。有两个默认命名空间，其中一个是空的......因此，我会完全避免（掩饰）它们。

DECLARE @xml XML= 
'<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
    <soap:Body> 
    <ExecCommandResponse xmlns="http://tempuri.org/"> 
     <ExecCommandResult> 
     <Result xmlns=""> 
      <row> 
      <LOT>VERL5B3002PL</LOT> 
      <ID>115</ID> 
      <WH>710</WH> 
      <STPL>12</STPL> 
      </row> 
      <row> 
      <LOT>VERL68804EVN</LOT> 
      <ID>3716</ID> 
      <WH>771</WH> 
      <STPL>6</STPL> 
      </row> 
     </Result> 
     </ExecCommandResult> 
    </ExecCommandResponse> 
    </soap:Body> 
</soap:Envelope>'; 


SELECT r.value('LOT[1]','varchar(max)') AS LOT 
     ,r.value('ID[1]','int') AS ID 
     ,r.value('WH[1]','int') AS WH 
     ,r.value('STPL[1]','int') AS STPL 
FROM @xml.nodes('/*:Envelope/*:Body/*:ExecCommandResponse/*:ExecCommandResult/*:Result/*:row') AS A(r) 

--or更简单（甚至会没有*:工作）：

SELECT r.value('LOT[1]','varchar(max)') AS LOT 
     ,r.value('ID[1]','int') AS ID 
     ,r.value('WH[1]','int') AS WH 
     ,r.value('STPL[1]','int') AS STPL 
FROM @xml.nodes('//*:row') AS A(r) 

总的来说，我会说：要尽可能具体，因此而建议第一...

Answer 2

尝试以下。看看它是否有效。

declare @xmldata xml = '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
          <soap:Body> 
           <ExecCommandResponse> 
            <ExecCommandResult> 
             <Result xmlns=""> 
              <row> 
               <LOT>VERL5B3002PL</LOT> 
               <ID>115</ID> 
               <WH>710</WH> 
               <STPL>12</STPL> 
              </row> 
              <row> 
               <LOT>VERL68804EVN</LOT> 
               <ID>3716</ID> 
               <WH>771</WH> 
               <STPL>6</STPL> 
              </row> 
             </Result> 
            </ExecCommandResult> 
           </ExecCommandResponse> 
          </soap:Body> 
         </soap:Envelope>' 
declare @readdoc as INT 

EXEC sp_xml_preparedocument @readdoc OUTPUT, @xmldata , '<root xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />' 

select LOT,ID,WH,STPL 
from OPENXML(@readdoc,'soap:Envelope/soap:Body/ExecCommandResponse/ExecCommandResult/Result/row') 
with 
( 
    LOT [varchar](50) 'LOT', 
    ID [varchar](50) 'ID', 
    WH [varchar](50) 'WH', 
    STPL [varchar](50) 'STPL' 
) 

EXEC sp_xml_removedocument @readdoc 
GO 

下面是输出。