时间戳

首页 > 解决方案 > 如何在 SQL 中使用 xml 命名空间通配符（表中具有不同命名空间 url 的多个 xml 文件）

sql-server - 如何在 SQL 中使用 xml 命名空间通配符（表中具有不同命名空间 url 的多个 xml 文件）

问题描述

我有一个 SQL 文件表，其中存储了多个 xml 文件以供在 SQL 中使用。xml 文件的内容不受我控制。我只需要将它们用作我的表格的输入。所有 xml 文件都具有相同的结构/设置。唯一的问题是我刚刚意识到其中一些 xml 文件具有不同的命名空间 url（因此在我的表中返回 NULL）。

我正在使用 xml 文件创建一个表，其中 xml 中的键作为列返回，属性作为行中的值返回。每个 xml 都作为行返回。

所以我现在遇到的问题是，对于所有具有（稍微）不同命名空间的 xml，整行都返回 NULL。

使用的命名空间是：http: //schemas.kvk.nl/xb/query/service/2016/1/0/0 和 http://schemas.kvk.nl/xb/query/service/2017/1/0 /0

我使用的查询：

WITH XMLNAMESPACES('http://schemas.kvk.nl/xb/query/service/2016/1/0/0' AS ns2) -- Pull namespaces for NS2
SELECT p.*
FROM
(
    SELECT [name]
    ,x.l.value('(ns2:opendataField[@key="SbiBusinessCode"]/@value)[1]','varchar(max)') AS SBI
    ,x.l.value('(ns2:opendataField[@key="FinancialYear"]/@value)[1]','varchar(max)') AS FY
    from dbo.XBRLft t -- filestream table
    CROSS APPLY(SELECT CAST(t.[file_stream] AS XML)) A(xbrl) -- convert filestream into xml.
    CROSS APPLY xbrl.nodes('/opendata') x(l) 
    where x.l.exist('./*/@key')=1 
    ) p

这将返回仅包含第一个命名空间的值的表（因为在查询中使用），但基于具有第二个命名空间的 xml 的每一行都返回 null。

所以我尝试使用通配符而不是命名空间，但这只会返回错误。

SELECT p.*
FROM
(
    SELECT [name]
    -- Putting all key's as columns and showing the attribute value in row.
    ,x.l.value('(//*:ns2:opendataField[@key="FinancialYear"]/@value)[1]','varchar(max)') AS FY
    from dbo.XBRLft t -- filestream table
    CROSS APPLY(SELECT CAST(t.[file_stream] AS XML)) A(xbrl) -- 
    CROSS APPLY xbrl.nodes('//*:opendata') x(l) -- 
    where x.l.exist('./*/@key')=1 -- 
    ) p

查询中有更多键，但在本示例中省略了这些键。

知道如何通过应用 2 个命名空间 url 或修复通配符来完成这项工作吗？

更新

这些是 2 个示例（每个 XML 中的 1 个具有不同的命名空间）。我不确定我是否可以使用 COALESCE，因为两个 XML 版本在整个 xml 中都使用 ns2（并且我需要在查询中添加整个路径以获得不同的键/值，因为 xml 是分层的）。

XML 1：

<opendata xmlns:ns2="http://schemas.kvk.nl/xb/query/service/2016/1/0/0">
  <ns2:opendataField key="DocumentAdoptionDate" value="2017-08-27" />
  <ns2:opendataField key="FinancialYear" value="2016" />
  <ns2:opendataField key="BalanceSheet">
    <ns2:opendataField key="BalanceSheetBeforeAfterAppropriationResultsTitle">
      <ns2:opendataField key="BalanceSheetBeforeAfterAppropriationResults" value="Na" />
    </ns2:opendataField>
    <ns2:opendataField key="BalanceSheetTitle">
      <ns2:opendataField key="Assets" value="548985">
        <ns2:opendataField key="AssetsNoncurrent" value="447571">
          <ns2:opendataField key="PropertyPlantEquipment" value="208215" />
          <ns2:opendataField key="FinancialAssets" value="239355" />
        </ns2:opendataField>
        <ns2:opendataField key="AssetsCurrent" value="101414">
          <ns2:opendataField key="Receivables" value="68895" />
          <ns2:opendataField key="CashAndCashEquivalents" value="32519" />
        </ns2:opendataField>
      </ns2:opendataField>
etc etc

XML2

<opendata xmlns:ns2="http://schemas.kvk.nl/xb/query/service/2017/1/0/0">
  <ns2:opendataField key="DocumentAdoptionDate" value="2019-03-11" />
  <ns2:opendataField key="FinancialYear" value="2017" />
  <ns2:opendataField key="BalanceSheet">
    <ns2:opendataField key="BalanceSheetBeforeAfterAppropriationResultsTitle">
      <ns2:opendataField key="BalanceSheetBeforeAfterAppropriationResults" value="Na" />
    </ns2:opendataField>
    <ns2:opendataField key="BalanceSheetTitle">
      <ns2:opendataField key="Assets" value="184535">
        <ns2:opendataField key="AssetsCurrent" value="184535">
          <ns2:opendataField key="AssetsCurrentOther" value="184535" />
        </ns2:opendataField>
      </ns2:opendataField>
etc etc

因此，要获取我需要的键和值，查询如下所示：

 SELECT [name]
    -- Putting all key's as columns and showing the attribute value in row.
    ,x.l.value('(ns2:opendataField[@key="SbiBusinessCode"]/@value)[1]','varchar(max)') AS SBI
    ,x.l.value('(ns2:opendataField[@key="FinancialYear"]/@value)[1]','varchar(max)') AS FY
    ,x.l.value('(ns2:opendataField/ns2:opendataField/ns2:opendataField[@key="Assets"]/@value)[1]','varchar(max)') AS Assets
    ,x.l.value('(ns2:opendataField/ns2:opendataField/ns2:opendataField/ns2:opendataField[@key="AssetsNoncurrent"]/@value)[1]','varchar(max)') AS AssetsNoncurrent
    ,x.l.value('(ns2:opendataField/ns2:opendataField/ns2:opendataField/ns2:opendataField/ns2:opendataField[@key="IntangibleAssets"]/@value)[1]','varchar(max)') AS IntangibleAssets
    ,x.l.value('(ns2:opendataField/ns2:opendataField/ns2:opendataField/ns2:opendataField/ns2:opendataField[@key="PropertyPlantEquipment"]/@value)[1]','varchar(max)') AS PropertyPlantEquipment

我不确定如何在这里应用您的 COALESCE 示例，因为这两种 XML 类型都有 ns2 ，我需要整个路径..

再次感谢！

标签： sql-serverxmltsql

解决方案

需要命名空间以避免相同名称之间的歧义。从这个角度来看，使用通配符可能非常危险，并可能导致意想不到的结果......

试试这个：

带有一些测试数据的虚拟表

DECLARE @tbl TABLE(id INT IDENTITY, YourXml XML);
INSERT INTO @tbl VALUES
('<root xmlns="blah1">
 <test>Test in 1</test>
 </root>'),
('<root xmlns="blah2">
 <test>Test in 2</test>
 </root>');

-- 这是你的问题：我们定义了默认命名空间，它只适用于案例 1：

WITH XMLNAMESPACES(DEFAULT 'blah1')
SELECT t.id
      ,t.YourXml.value('(/root/test/text())[1]','nvarchar(100)') AS ContentOfTest
FROM @tbl t;

--但是我们可以使用两个前缀命名空间并使用 COALESCE 来返回一个返回值的命名空间：

WITH XMLNAMESPACES('blah1' AS ns1
                  ,'blah2' AS ns2)
SELECT t.id
      ,COALESCE(
       t.YourXml.value('(/ns1:root/ns1:test/text())[1]','nvarchar(100)') 
      ,t.YourXml.value('(/ns2:root/ns2:test/text())[1]','nvarchar(100)') 
       ) AS ContentOfTest
FROM @tbl t

--这是使用通配符的方法，如果你可以肯定的话，这不会导致歧义：

SELECT t.id
      ,t.YourXml.value('(/*:root/*:test/text())[1]','nvarchar(100)') AS ContentOfTest
FROM @tbl t

推荐阅读