Fetch XML node value with namespace

Question

I have to get values of company node elements.I have tried all the method to fetch data from the node but no luck. Below is my 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>(999) 999-9999</CompanyTelephone>
        </Company>
        <CustomerId>33642</CustomerId>    
      </Customer>
      </CompanyInvoice>

TSQL Code: I have simply tried with this, but not getting any updates

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>(999) 999-9999</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)

You need to introduce and use namespaces in your query. See learn.microsoft.com/en-us/sql/relational-databases/xml/… — Serg
– Serg, Commented Sep 1, 2017 at 7:28
But you haven't mentioned x namespace in the nodes ...nodes('CompanyInvoice/x:Customer/x:Company') ... — Serg
– Serg, Commented Sep 1, 2017 at 7:50

Serg · Accepted Answer · 2017-09-01 08:09:05Z

5

Basically you have two options. 1.Introduce and use namespaces properly. Pay attention to namespaces scope. 2.Use a wildcard namespace (not recommended in production)

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>(999) 999-9999</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);

answered Sep 1, 2017 at 8:09

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sachin Over a year ago

Thanks for this, But why we don't use wild card in production

Serg Over a year ago

Generally xml comes from another loosely coupled system and we don't want any bad stuff entering our DB. As namespaces are an essential part of the data exchange contract, we'd better strictly define them the way contract prescribes.

Tanmay Nehete · Accepted Answer · 2017-09-01 07:31:35Z

1

Try This this just and other example

REferThis for more info

  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)

If this dosent work then Alternate Link

answered Sep 1, 2017 at 7:31

Tanmay Nehete

2,2164 gold badges33 silver badges45 bronze badges

1 Comment

sachin Over a year ago

Thanks for response but this not help me to achieve my result. I have added my tsql code. Try to get values on those

Collectives™ on Stack Overflow

Fetch XML node value with namespace

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related