CODEWITHSUNDEEP
sqlsql-serverxmlopenrowset
Yash
Yash

Reputation: 191

Select specific element from XML with XML Namespace

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

Upvotes: 2

Views: 721

Answers (1)

TT.
TT.

Reputation: 16146

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;

Upvotes: 2

Related Questions