How to get specific XML namespace in XQuery in SQL Server

Question

I have a XML that I need one specific namespace according to node like temprature with hls i need namespace of that "http://www.schema.hls.com/extension" I have tried with these

DECLARE @EventXML AS XML

SET @EventXML='<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns:test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ns="urn:global:test:xsd:1" 
xmlns:hls="http://schema.hls.com/extension" creationDate="2007-01-25T00:00:00Z"
schemaVersion="1.0">
<TestBody>
<TestList>
<TestEvent>
    <hls:temperature>20</hls:temperature>
  </TestEvent>
</TestList>
</TestBody>
</ns:test>'

SELECT 
OE.value('@ns','varchar(50)') + '#' + OE.value('fn:local-name(.)[1]','varchar(50)'),
OE.value('@id','varchar(50)'),
CONVERT(VARCHAR(4000),CASE WHEN OE.exist('./*') =1 THEN OE.query('./*')  ELSE    
OE.value('./text()[1]','varchar(100)') END)
FROM @EventXML.nodes('//TestEvent/*') TestEvent(OE)
WHERE OE.value('fn:local-name(.)[1]','varchar(50)') IN --(@tag) 
(SELECT  Split.a.value('.', 'VARCHAR(100)') AS extag 
FROM  (SELECT   CONVERT(XML,'<M>' + REPLACE(ISNULL('temperature','0'), ',', '</M><M>') + '</M>') AS String 
 ) AS A CROSS APPLY String.nodes ('/M') AS Split(a))

I am using these in SQL query window but getting only third column value 20 not get namespace by @ns

Please suggest how to get the namespace

OE.value('@ns','varchar(50)')

by these.

thanks in advanced.

Please edit your question and make clear what you actually want. In your question and the title you say you want to get the XML namespace. But as marc_s most likely correctly guessed, I would say you want some item within a specific namespace. Is this correct or do you actually need the XML namespace URI (that's how your question is phrased; however, this does not make much sense in almost all cases)? — dirkk
– dirkk, Commented Jul 31, 2014 at 17:51

marc_s · Accepted Answer · 2014-07-31 16:27:26Z

Your code and XML somehow just don't quite match up - and the query is really quite confusing....

If you want to fetch the data, you must respect the XML namespaces in play. You need to declare them with a WITH XMLNAMESPACES() construct, and you need to use them in your XPath.

But also: the node you're selecting (<hls:temperature>) doesn't really have any id and ns attributes..... so of course you're not getting any values!

I tried to use a trimmed down version and I added the two attributes - just to show how to use the XML namespaces stuff in your code.

Here it comes:

DECLARE @EventXML AS XML

SET @EventXML = 
   '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ns:test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xmlns:ns="urn:global:test:xsd:1" 
             xmlns:hls="http://schema.hls.com/extension" 
             creationDate="2007-01-25T00:00:00Z" schemaVersion="1.0">
       <TestBody>
          <TestList>
             <TestEvent>
                <hls:temperature ns="test" id="42">20</hls:temperature>
             </TestEvent>
         </TestList>
      </TestBody>
   </ns:test>'

-- define your XML namespaces that are in play. 
-- You *MUST* match the namespace definition, but the *prefixes* that you define
-- can be something else entirely than in the XML document!
-- Of course, inside your XPath, you *MUST* use the defined prefixes!
;WITH XMLNAMESPACES('urn:global:test:xsd:1' AS x1, 
                    'http://schema.hls.com/extension' AS x2)
SELECT 
    OE.value('@ns', 'varchar(50)'),
    OE.value('@id', 'varchar(50)')
FROM 
    @EventXML.nodes('/x1:test/TestBody/TestList/TestEvent/x2:*') TestEvent(OE)

This code - using the XML namespaces defined and used in your XML - produces this output:

(No column name)  (No column name)
test                   42

So this shows how you can access the attributes - if they are present! - on your XML nodes, even with the presence of XML namespaces.

Collectives™ on Stack Overflow

How to get specific XML namespace in XQuery in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related