Select specific element from XML with XML Namespace

Question

I have an XML file stored in a SQL table. Now I want to read z specific element value using SQL.

I can read a specific element value from the XML if the XML does not have namespace element in it. I want to read XML file with namespace.

Here is my XML file:

<currency:Envelope xmlns="http://www.currency.com/eurofxref" xmlns:currency="http://www.currency.org/xml/2002-08-01">
 <Cube>
  <Cube time="2016-11-04">
   <Cube currency="USD" rate="1.1093" />
   <Cube currency="JPY" rate="114.24" />
   <Cube currency="BGN" rate="1.9558" />
   <Cube currency="CZK" rate="27.021" />      
  </Cube>
 </Cube>
</currency:Envelope>

Here is the SQL code for reading the values:

CREATE TABLE XMLwithOpenXML
(
    Id INT IDENTITY PRIMARY KEY,
    XMLData XML,
    LoadedDateTime DATETIME
)

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'E:\currency.xml', SINGLE_BLOB) AS x;

SELECT XMLData.value('(Cube/Cube/Cube[@currency="USD"]/@rate)[1]','VARCHAR(MAX)') 
FROM XMLwithOpenXML

TT. · Accepted Answer · 2016-11-05 13:28:42Z

2

You need to select in the proper XML Namespace. You can declare the namespace using WITH XMLNAMESPACES and associate it with a prefix. Then use this prefix when referring to elements in the namespace.

WITH 
    XMLNAMESPACES('http://www.currency.com/eurofxref' AS efr)
SELECT  
    rate=XMLData.value('(//efr:Cube/efr:Cube/efr:Cube[@currency="USD"]/@rate)[1]','VARCHAR(MAX)') 
FROM
    XMLwithOpenXML;

edited Nov 5, 2016 at 13:28

answered Nov 5, 2016 at 13:20

TT.

16.2k6 gold badges53 silver badges95 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

+1 from my side. Just to mention: need to select is a bit to strict, as there was the possibility of using a wildcard (.value('(*:Envelope/*:Cube/*:Cube/*:Cube[@*:currency="USD"]/@*:rate)[1]','nvarchar(max)')), but being more explicit is better. Therefore I'd avoid the // in the beginning too...

TT. Over a year ago

@Shnugo Hi Shnugo! Indeed, need to select is a bit too strict perhaps. I'm echoing the general advice when you don't know whether there are multiple namespaces that define the same element-names. But in this case with only one namespace you could opt for the wildcard namespace qualifier.

Collectives™ on Stack Overflow

Select specific element from XML with XML Namespace

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related