問題描述

我需要在 SQL Server 中解析一個 SOAP xml 并將其轉換為表

I need to parser a SOAP xml in SQL Server and convert it to table

<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 并將其轉換為表

I need to parser a SOAP xml in sql server and convert it to table

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

推薦答案

使用最新函數來查詢 XML.

Use the up-to-date functions to query XML.

您的 XML 在命名空間上看起來不是很干凈.有兩個默認命名空間，其中一個是空的...因此我會完全避免(屏蔽)它們.

Your XML is not very clean looking on the namespaces. There are two default namespaces, one of them empty... Therefore I would avoid (mask) them entirely.

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 even simpler (would even work without the *:):

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)

總的來說，我會說:盡可能具體，因此建議第一個......

In general I'd say: Be as specific as possible, therefore rather suggest the first...

這篇關于如何在 SQL Server 中解析 SOAP XML 并顯示為表格的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！