我有一个 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，我需要整个路径。
再次感谢！
需要命名空间以避免相同名称之间的歧义。从这个角度来看，使用通配符可能非常危险，并可能导致意想不到的结果......
试试这个：
带有一些测试数据的虚拟表
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句