How to Get specific NameSpace from xml by xquery in sql server



I have one xml i need one specific namespace 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://ift.tt/ra1lAU"
xmlns:ns="urn:global:test:xsd:1"
xmlns:hls="http://ift.tt/1rMgGY5" 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.


No comments:

Post a Comment