如何在SQL Server中获取具有命名空间的XML节点值？

Question

我需要获取公司节点元素的值。我已经尝试了所有获取节点数据的方法，但都没有成功。以下是我的XML。

<?xml version="1.0"?>
    <CompanyInvoice xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Customer xmlns="http://t.service/CompanyServices/">
        <Company>
          <CompanyId>10001</CompanyId>
          <CoastalId>454564564564564564564564565465454546565555555</CoastalId>
          <CompanyFederalId>345345</CompanyFederalId>
          <CompanyName>Anytime Home</CompanyName>
          <CompanyAddress>Address1</CompanyAddress>
          <CompanyCity>TR</CompanyCity>
          <CompanyState>UT</CompanyState>
          <CompanyPostalCode>11</CompanyPostalCode>
          <CompanyCountry>IT</CompanyCountry>
          <CompanyTelephone>[phone]</CompanyTelephone>
        </Company>
        <CustomerId>33642</CustomerId>    
      </Customer>
      </CompanyInvoice>

TSQL 代码： 我尝试使用以下代码，但没有得到任何更新。

Declare @DATAXML xml ='<?xml version="1.0"?>
    <CompanyInvoice xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Customer xmlns="http://t.service/CompanyServices/">
        <Company>
          <CompanyId>10001</CompanyId>
          <CoastalId>454564564564564564564564565465454546565555555</CoastalId>
          <CompanyFederalId>345345</CompanyFederalId>
          <CompanyName>Anytime Home</CompanyName>
          <CompanyAddress>Address1</CompanyAddress>
          <CompanyCity>TR</CompanyCity>
          <CompanyState>UT</CompanyState>
          <CompanyPostalCode>11</CompanyPostalCode>
          <CompanyCountry>IT</CompanyCountry>
          <CompanyTelephone>[phone]</CompanyTelephone>
        </Company>
        <CustomerId>33642</CustomerId>    
      </Customer>
      </CompanyInvoice>'


;WITH XMLNAMESPACES('http://t.service/CompanyServices/' as x)
Select
a.value('x:CompanyId[1]','nvarchar(50)') as CompanyId, 
a.value('x:CoastalId[1]','nvarchar(500)') as CoastalId, 
a.value('x:CompanyName[1]','nvarchar(500)') as CompanyName
From @DATAXML.nodes('/CompanyInvoice/Customer/Company')as a (a)

- sachin

---

https://dev59.com/hs-l0IgBFxS5KdRjueUm - Amit Kumar Singh

你需要在查询中引入和使用命名空间。请参阅 https://learn.microsoft.com/en-us/sql/relational-databases/xml/add-namespaces-to-queries-with-with-xmlnamespaces#using-with-xmlnamespaces-with-the-xml-data-type-methods - Serg

@Serg，我引入了命名空间，但没有成功。 - sachin

但是您在nodes中没有提到x命名空间 ...nodes('CompanyInvoice/x:Customer/x:Company') ... - Serg

@Serg，哦！！！愚蠢的错误。感谢您通知我。 - sachin

Answer 1

基本上你有两个选择。 1.正确地引入和使用命名空间。注意命名空间的作用域。 2.使用通配符命名空间（不建议在生产环境中使用）。

Declare @DATAXML xml = N'<?xml version="1.0"?>
    <CompanyInvoice xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Customer xmlns="http://t.service/CompanyServices/">
        <Company>
          <CompanyId>10001</CompanyId>
          <CoastalId>454564564564564564564564565465454546565555555</CoastalId>
          <CompanyFederalId>345345</CompanyFederalId>
          <CompanyName>Anytime Home</CompanyName>
          <CompanyAddress>Address1</CompanyAddress>
          <CompanyCity>TR</CompanyCity>
          <CompanyState>UT</CompanyState>
          <CompanyPostalCode>11</CompanyPostalCode>
          <CompanyCountry>IT</CompanyCountry>
          <CompanyTelephone>[phone]</CompanyTelephone>
        </Company>
        <CustomerId>33642</CustomerId>    
      </Customer>
      </CompanyInvoice>';


WITH XMLNAMESPACES('http://t.service/CompanyServices/' as x)
Select
a.value('x:CompanyId[1]','nvarchar(50)') as CompanyId, 
a.value('x:CoastalId[1]','nvarchar(500)') as CoastalId, 
a.value('x:CompanyName[1]','nvarchar(500)') as CompanyName
From @DATAXML.nodes('CompanyInvoice/x:Customer/x:Company')as a (a);

-- 

select t.node.value('*:CompanyId[1]', 'int')
from @DATAXML.nodes('*:CompanyInvoice/*:Customer/*:Company') t(node);

- Serg

---

谢谢这个，但是为什么我们不在生产中使用通配符？ - sachin

通常情况下，XML来自另一个松散耦合的系统，我们不希望任何不良内容进入我们的数据库。由于命名空间是数据交换协议的重要组成部分，因此我们最好按照协议规定严格定义它们。 - Serg

Answer 2

尝试这个，这只是一个示例和其他示例

了解更多信息，请参考此处

  DECLARE @foo XML

SELECT @foo = N'
<harrys>
    <harry>
        <fish>0.015000000000</fish>
        <bicycle>2008-10-31T00:00:00+01:00</bicycle>
        <foo>ü</foo>
    </harry>
    <harry>
        <fish>0.025000000000</fish>
        <bicycle>2008-08-31T00:00:00+01:00</bicycle>
        <foo>ä</foo>
    </harry>
</harrys>
'

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/*') x(item)
    CROSS APPLY
    x.item.nodes('./*') AS y(item)

SELECT
    CAST(CAST(x.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(x.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(x.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('harrys/harry') x(item)

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/harrys') x(item)
    CROSS APPLY
    x.item.nodes('./harry') AS y(item)

如果这个不起作用，那么请使用备用链接。

- Tanmay Nehete

---

谢谢您的回复，但这并没有帮助我实现我的结果。我已经添加了我的T-SQL代码，请尝试获取这些值。 - sachin