时间戳

首页 > 解决方案 > 将 XML 解析为具有不同 XML 树级别元素的 SQL

sql - 将 XML 解析为具有不同 XML 树级别元素的 SQL

问题描述

我有一个问题，我需要将一堆 XML 文件解析到 SQL 数据库中，其中所需的元素都位于 XML 文件的不同分支中。到目前为止，我在 Google 上搜索和查看的每个示例都处理相当简单的 XML 树。

这是 XML 文件标头中引用的架构：http ://service.ddex.net/xml/ern/37/release-notification.xsd

简化的 XML：

<store>
  <bookstore>
    <book>
      <ref_title>
        <title>Harry Potter</title>
      </ref_title>
      <ref_author>
       <author>J K. Rowling</author>
      </ref_author>
      <year>
        <this_year>2005</this_year>
      </year>
      <price>
        <dollar>usd</dollar>
        <value>29.99</value>
      </price>
      <price>
        <dollar>aud</dollar>
        <value>49.99</value>
      </price>
    </book>
    <book>
      <ref_title>
        <title>Petes Book of Pie</title>
      </ref_title>
      <ref_author>
       <author>Pete P</author>
      </ref_author>
      <year>
        <this_year>1999</this_year>
      </year>
      <price>
        <dollar>usd</dollar>
        <value>19.99</value>
      </price>
      <price>
        <dollar>aud</dollar>
        <value>39.99</value>
      </price>
    </book>
  </bookstore>
</store>

我需要结束这个：

TITLE               AUTHOR          YEAR        DOLLAR      VALUE
Harry Potter        J K. Rowling    2005        usd         49.99
Petes Book of Pie   Pete P          1999        usd         19.99

我正在使用 Microsoft SQL 2019 并希望能够在 SQL-T 中完成这一切，但我也看过 Python 示例，但没有运气。

有小费吗？

标签： sqlsql-serverxml

解决方案

你需要nodes在你的 XML 数据上使用CROSS APPLY. 这似乎让你得到你所追求的：


USE Sandbox;
GO
DECLARE @XML xml = '<store>
  <bookstore>
    <book>
      <ref_title>
        <title>Harry Potter</title>
      </ref_title>
      <ref_author>
       <author>J K. Rowling</author>
      </ref_author>
      <year>
        <this_year>2005</this_year>
      </year>
      <price>
        <dollar>usd</dollar>
        <value>29.99</value>
      </price>
      <price>
        <dollar>aud</dollar>
        <value>49.99</value>
      </price>
    </book>
    <book>
      <ref_title>
        <title>Petes Book of Pie</title>
      </ref_title>
      <ref_author>
       <author>Pete P</author>
      </ref_author>
      <year>
        <this_year>1999</this_year>
      </year>
      <price>
        <dollar>usd</dollar>
        <value>19.99</value>
      </price>
      <price>
        <dollar>aud</dollar>
        <value>39.99</value>
      </price>
    </book>
  </bookstore>
</store>';

SELECT bs.b.value('(ref_title/title/text())[1]','nvarchar(50)') AS Title,
       bs.b.value('(ref_author/author/text())[1]','nvarchar(50)') AS Author,
       bs.b.value('(year/this_year/text())[1]','nvarchar(50)') AS [Year],
       p.d.value('(./text())[1]','nvarchar(50)') AS Dollar,
       p.d.value('(../value/text())[1]','nvarchar(50)') AS [Value]
FROM (VALUES(@XML))V(X)
     CROSS APPLY V.X.nodes('/store/bookstore/book') bs(b)
     CROSS APPLY bs.b.nodes('price/dollar') p(d)
WHERE p.d.value('(./text())[1]','nvarchar(50)') = 'usd';

推荐阅读