具有默认命名空间的 SQL Server XQuery

2021-09-10 00:00:00 xml tsql xquery sql-server

我在 XML 列的 SQL Server 表中有一些 XML 数据，如下所示:

I've got some XML Data in a SQL Server Table in an XML Column as follows:

<AffordabilityResults>
      <matchlevel xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">IndividualMatch</matchlevel>
      <searchdate xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">2013-07-29T11:20:53</searchdate>
      <searchid xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">{E40603B5-B59C-4A6A-92AB-98DE83DB46E7}</searchid>
      <calculatedgrossannual xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">13503</calculatedgrossannual>
  <debtstress xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">
    <incomedebtratio>
      <totpaynetincome>0.02</totpaynetincome>
      <totamtunsecured>0.53</totamtunsecured>
      <totamtincsec>0.53</totamtincsec>
    </incomedebtratio>
  </debtstress>
</AffordabilityResults>

您会注意到有些元素具有 xmlns 属性，有些则没有...

You'll note that some of the elements have an xmlns attribute and some don't...

我需要编写查询来返回数据 - 更重要的是向业务分析师展示如何编写自己的查询来获取她需要的数据，因此我希望它尽可能简单.

I need to write queries to return the data - and more importantly show a business analyst how to write her own queries to get the data she needs so I want it to be as simple as possible.

我可以使用 WITH XMLNAMESPACES 元素轻松查询数据，如下所示:

I can query the data easily using the WITH XMLNAMESPACES element as follows:

WITH XMLNAMESPACES (N'urn:callcredit.co.uk/soap:affordabilityapi2' as x )
SELECT

    ResponseXDoc.value('(/AffordabilityResults/x:matchlevel)[1]','varchar(max)' ) AS MatchLevel
  , ResponseXDoc.value('(/AffordabilityResults/x:debtstress/x:incomedebtratio/x:totamtunsecured)[1]','nvarchar(max)' ) AS UnsecuredDebt

  FROM [NewBusiness].[dbo].[t_TacResults]

但是将 x: 部分添加到查询中会使其看起来过于复杂，我希望对业务分析师保持简单.

But adding the x: part to the query makes it look overly complicated, and I want to keep it simple for the business analyst.

我尝试添加:

WITH XMLNAMESPACES (DEFAULT 'urn:callcredit.co.uk/soap:affordabilityapi2' )

并从 XQuery 中删除 x: - 但这会返回 null(可能是因为根元素上缺少 xmlns?)

and removing the x: from the XQuery - but this returns null (possibly because of the lack of the xmlns on the root element?)

有什么方法可以简化这些查询，无论是否使用默认命名空间?

Is there any way I can simplify these queries either with or without the default namespace?

推荐答案

如果命名空间在你的用例中并不重要，你可以使用命名空间通配符选择器 *:，它既选择不带和具有任意命名空间.

If namespaces are not important in your use case, you could use the namespace wildcard selector *:, which both selects nodes without and with arbitrary namespaces.

一个示例查询可能是

(/*:AffordabilityResults/*:matchlevel)[1]

业务分析师仍然需要在每个节点测试之前添加选择器，但它始终是相同的前缀"，唯一预期的错误是忘记在某处使用它.

The business analyst will still have to add the selector in front of every node test, but it's the same "prefix" all the time and the only error to be expected is forgetting to use it somewhere.

相关文章