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

How to parse SOAP XML in SQL Server and show as table(如何在 SQL Server 中解析 SOAP XML 并显示为表格)

问题描述

我需要在 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 并显示为表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！