主页 / dba / 问题 / 121852
Accepted
AcePL
Asked: 2015-11-24 05:07:59 +0800 CST

使用默认命名空间和不一致的根节点解析类型化 XML

  • 772

我有一个结构化的类型化 xml 文件。

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
 <NewLeads>
  <OutputSchema>
  <root xmlns="" type="array">
   <item type="object">
   <SaleProperty type="object">
    <Type type="string">Freehold</Type>
    <PostDistrict type="string">xxx</PostDistrict>
    <Address type="string">address</Address>
     <Value type="number">17.0</Value>
   </SaleProperty>
   <Transaction type="string">SaleOnly</Transaction>
   <Quote type="object">
    <Sale type="object">
     <Fees type="number">450.0</Fees>
     <Disbursements type="number">0.0</Disbursements>
     <StampDuty type="number">0.0</StampDuty>
     <Total type="number">450.0</Total>
    </Sale>
   <Discount type="number">0.0</Discount>
   <VAT type="number">90.0</VAT>
   <Total type="number">540.0</Total>
  </Quote>
  <MoverId type="number">12345678</MoverId>
  <Name type="string">Mr AS</Name>
  <Email type="string">[email protected]</Email>
  <Telephone type="string">0123456789</Telephone>
  <Comments type="string">Joint ownership</Comments>
  <EstimatedMoveDate type="string">2015-11-25T05:57:00</EstimatedMoveDate>
  <Charge type="number">4.99</Charge>
  <ChargeStatus type="string">Chargeable</ChargeStatus>
 </item>
</root>
</OutputSchema></NewLeads></WebService>

此外，对象 SaleProperty 可能会丢失，而可能是对象 PurchaseProperty。同样，对象 Quote 可以同时包含销售、购买、再抵押对象或销售和购买我找不到通过 SQL Server 2012 上的 xquery 将其插入到一个表中的任何提示。基本问题是每次我构建查询以提取除了我得到的错误之外，即使是一个值（即事务）也是空字符串。

我的示例查询（基于 MSDN 示例）：

SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/root/item/transaction') as t(c)

我想举个例子，如何将此 xml 插入表中（每个可能的元素都有自己的列。

我认为问题出在名称空间和 xml 的强类型上。但是，这是我从 Web 服务中得到的。我在 xml 上工作，在根元素中缺少和包含第二个 xmlns 声明，以及将结构修剪到根元素。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    我可以看到您的 XQuery 的三个问题是（这些都与 Typed XML 无关）：

    1. 您没有指定/root节点的正确路径。它应该是：

      SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/transaction') as t(c)

    2. XML 区分大小写，因此您需要为“Transaction”节点使用大写的“T”：

      SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/Transaction') as t(c)

    3. 虽然这些修复可能会让您返回 1 个值（在这种情况下，它应该是“SaleOnly”），但它不会遍历“item”节点，因为您在提供给.nodes()函数的路径中过于具体。相反，该规范的最终节点应该是“item”，这是您要迭代的内容。在这种情况下，您将“事务”部分移至.value()函数：

      SELECT t.c.value('(./Transaction)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)

    关于声明：

    我在 xml 上工作，在根元素中缺少和包含第二个 xmlns 声明，以及将结构修剪到根元素。

    “修剪到根的结构”应该可以通过删除第一个/和/右边之间的所有内容/root...（即WebService/NewLeads/OutputSchema）来处理。所以得到的路径是：

    from @ixml.nodes('//root/item') as t(c)

    注意：
    我无法使用<WebService>元素中声明的命名空间来 100% 工作（请参阅附加说明，因为情况不再如此）。把它拿出来就可以了。给它一个前缀，例如xmlns:something="http://www.orbis-software.com/WebSvcCon"使它工作，但是需要在方法中声明。我现在可以让它工作的唯一方法是在每个 XML 函数（.nodes和.value）中声明默认命名空间，如下所示：

    SELECT t.c.value('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  (./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  /WebService/NewLeads/OutputSchema/root/item') as t(c)

    注意 2：
    更好的是，您可以使用WITH XMLNAMESPACES声明一个或多个命名空间以用于整个查询，因此无需在每个 XML 函数中定义。以下两者都有效：

    ;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)


;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

    但是，请记住，如果文档缺少<WebService xmlns="http://www.orbis-software.com/WebSvcCon">元素并因此没有默认命名空间，那么您需要删除该;WITH XMLNAMESPACES部分。当然，如果<root>元素有自己的默认命名空间，那么您可能需要保留它。既然您知道这些部分之间的联系，您就可以玩弄它直到它起作用。

    注意 3：
    如果您最终声明了两个默认命名空间——一个在<WebService>元素中，一个在元素中——那么<root>你需要指定 URI<root xmlns="bob"> 和//语法而不是完全限定的路径。因此，如果您的 XML 看起来像：

    <WebService xmlns="http://www.orbis-software.com/WebSvcCon">
 <NewLeads>
  <OutputSchema>
  <root xmlns="http://someplace" type="array">

    然后，您将使用：

    ;WITH XMLNAMESPACES (DEFAULT 'http://someplace')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

    <WebService>但是，如果您确实拥有该元素但该<root>元素缺少xmlns声明，那将无济于事。<WebService>在这种情况下，您仍然需要指定元素中注明的命名空间。有趣，有趣，有趣:-)。

    注意 4：
    更好的是：结合@wBob 的回答中提到的内容，我们实际上可以摆脱该;WITH XMLNAMESPACES子句，而是使用命名空间通配符。您只需在每个XML 函数的每个节点前加上. 现在查询应该如下所示：*:

    SELECT t.c.value('(./*:Transaction)[1]','varchar(50)') AS [type],
       t.c.value('(./*:SaleProperty/*:PostDistrict)[1]','varchar(50)') AS [PostDistrict]
FROM  @ixml.nodes('//*:root/*:item') t(c);

    这样做意味着查询适用于您的所有场景：

    1. 以“WebService”节点开始的完整结构，第二个 xmlns 声明：

      <WebService xmlns="http://www.orbis-software.com/WebSvcCon">
  <NewLeads>
    <OutputSchema>
      <root xmlns="uri" type="array">

    2. 以“WebService”节点开头的完整结构，单个 xmlns 声明：

      <WebService xmlns="http://www.orbis-software.com/WebSvcCon">
  <NewLeads>
    <OutputSchema>
      <root type="array">

    3. 以“根”节点开头的精简结构，单个 xmlns 声明：

      <root xmlns="uri" type="array">
    • 9

  2. 如果您只想将所有元素值转储为行而不考虑名称空间，那么您可以使用名称空间通配符，例如：

    DECLARE @xml XML = '<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
    <NewLeads>
        <OutputSchema>
            <root xmlns="" type="array">
                <item type="object">
                    <SaleProperty type="object">
                        <Type type="string">Freehold</Type>
                        <PostDistrict type="string">xxx</PostDistrict>
                        <Address type="string">address</Address>
                        <Value type="number">17.0</Value>
                    </SaleProperty>
                    <Transaction type="string">SaleOnly</Transaction>
                    <Quote type="object">
                        <Sale type="object">
                            <Fees type="number">450.0</Fees>
                            <Disbursements type="number">0.0</Disbursements>
                            <StampDuty type="number">0.0</StampDuty>
                            <Total type="number">450.0</Total>
                        </Sale>
                        <Discount type="number">0.0</Discount>
                        <VAT type="number">90.0</VAT>
                        <Total type="number">540.0</Total>
                    </Quote>
                    <MoverId type="number">12345678</MoverId>
                    <Name type="string">Mr AS</Name>
                    <Email type="string">[email protected]</Email>
                    <Telephone type="string">0123456789</Telephone>
                    <Comments type="string">Joint ownership</Comments>
                    <EstimatedMoveDate type="string">2015-11-25T05:57:00</EstimatedMoveDate>
                    <Charge type="number">4.99</Charge>
                    <ChargeStatus type="string">Chargeable</ChargeStatus>
                </item>
            </root>
        </OutputSchema>
    </NewLeads>
</WebService>'


-- Show all elements and their values irrespective of namespace
SELECT 
    x.y.value('local-name(..)', 'VARCHAR(MAX)') parentElementName,
    x.y.value('local-name(.)', 'VARCHAR(MAX)') elementName,
    x.y.value('.', 'VARCHAR(MAX)') elementValue
FROM @xml.nodes('//*[not(*)]') AS x(y)


SELECT 
    ws.c.value('local-name(..)', 'VARCHAR(MAX)') parentElementName,
    ws.c.value('local-name(.)', 'VARCHAR(MAX)') elementName,
    ws.c.value('.', 'VARCHAR(MAX)') elementValue
FROM @xml.nodes('*:WebService/*:NewLeads/*:OutputSchema/*:root/*:item/*[not(*)]') AS ws(c)

    结果：

    结果

    然后，如果您需要将其转换为列，则可以执行诸如动态枢轴之类的操作。如果您确实想明确指定元素，您可以使用类似的技术。

    从您的问题来看，您可能需要花更多时间学习 XML 命名空间，所以从这里开始：

    使用 WITH XMLNAMESPACES 添加命名空间 http://msdn.microsoft.com/en-us/library/ms177400.aspx

    • 4

相关问题