时间戳

首页 > 解决方案 > 将单列单行 XML 数据转换为 SQL 表

sql - 将单列单行 XML 数据转换为 SQL 表

问题描述

我有一个名为 XMLTest 的表，其中有一列名为 Val，数据类型为 XML

create table XMLTest(Val XML)

在这张表中，我读到了这些数据：

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope">
  <SOAP-ENV:Body>
    <ns0:getInterestAndExchangeRatesResponse xmlns:ns0="http://swea.riksbank.se/xsd">
      <return xmlns="">
        <datefrom xmlns="">2020-03-25</datefrom>
        <dateto xmlns="">2020-03-26</dateto>
        <groups xmlns="">
          <groupid xmlns="">130</groupid>
          <groupname xmlns="">Currencies against Swedish kronor</groupname>
          <series xmlns="">
            <seriesid xmlns="">SEKEURPMI</seriesid>
            <seriesname xmlns="">1 EUR</seriesname>
            <unit xmlns="">1.0E0</unit>
            <resultrows xmlns="">
              <date xmlns="">2020-03-25</date>
              <period xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <min xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <average xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <max xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <ultimo xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <value xmlns="">1.08823E1</value>
            </resultrows>
          </series>
        </groups>
      </return>
    </ns0:getInterestAndExchangeRatesResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

所以最终的结果是所有这些数据都在单列​​和单行中。我试图从这个 XML 中提取相关数据。我对 seriesid 及其价值感兴趣。所以最终的输出是：

seriesid | value
SEKEURPMI| 1.08823E1

最终会有多个结果会更像：

seriesid | value
SEKEURPMI| 1.08823E1
SEKUSDPMI| 1.3823E1
....     | ...

但是，我在创建有效查询以获取此信息时遇到问题。我一直在尝试的解决方案都没有与我的解决方案一起工作，因为我是直接从表中查询的。我一直在尝试实施此解决方案：如何将数据从 xml 保存到 sql 2008？但在从表中查询时，我似乎无法应用相同的技术。

标签： sqlsql-serverxmltsql

解决方案

这就是你所追求的吗？命名空间的声明在这里非常重要：

DECLARE @XML xml = '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope">
  <SOAP-ENV:Body>
    <ns0:getInterestAndExchangeRatesResponse xmlns:ns0="http://swea.riksbank.se/xsd">
      <return xmlns="">
        <datefrom xmlns="">2020-03-25</datefrom>
        <dateto xmlns="">2020-03-26</dateto>
        <groups xmlns="">
          <groupid xmlns="">130</groupid>
          <groupname xmlns="">Currencies against Swedish kronor</groupname>
          <series xmlns="">
            <seriesid xmlns="">SEKEURPMI</seriesid>
            <seriesname xmlns="">1 EUR</seriesname>
            <unit xmlns="">1.0E0</unit>
            <resultrows xmlns="">
              <date xmlns="">2020-03-25</date>
              <period xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <min xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <average xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <max xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <ultimo xmlns:ns1="http://www.w3.org/2001/XMLSchema-instance" xmlns="" ns1:nil="true" />
              <value xmlns="">1.08823E1</value>
            </resultrows>
          </series>
        </groups>
      </return>
    </ns0:getInterestAndExchangeRatesResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';

WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS [SOAP-ENV],'http://swea.riksbank.se/xsd' AS ns0, 'http://www.w3.org/2001/XMLSchema-instance' AS ns1)
SELECT V.X.value('(SOAP-ENV:Envelope/SOAP-ENV:Body/ns0:getInterestAndExchangeRatesResponse/return/groups/series/seriesid/text())[1]','varchar(10)') AS seriesid,
       V.X.value('(SOAP-ENV:Envelope/SOAP-ENV:Body/ns0:getInterestAndExchangeRatesResponse/return/groups/series/resultrows/value/text())[1]','float') AS [value]
FROM (VALUES(@XML))V(X)

推荐阅读