current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
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 20 hours ago

1 Answer 1

up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.