SQL Server transform xml and parse single values but not with build-in functions

I am trying to parse and transform XML values in SQL Server 2016. I can not use the the sp_xml_preparedocument or sp_xml_removedocument built-in procedures because I want to have it in a table value function.

I would like to select these 4 values:

• testXML…821327282
• Test value
• Test attribute
• Test property

XML template value:

<?xml version="1.0" encoding="utf-8"?>
<stns1:testXML...821327282 xmlns:stns1="qaTESTXML">
   <myvalue>Test value</myvalue>
   <myattribute>Test attribute</myattribute>
   <myproperty>Test property</myproperty>
</stns1:testXML...821327282>

My T-SQL try > code:

select
    [xml].[node].query('local-name(/ROOT[0]/id)') as [id],
    [xml].[node].query('local-name(/ROOT[0]/myvalue)') as [value],
    [xml].[node].query('local-name(/ROOT[0]/myattribute)') as [attribute],
    [xml].[node].query('local-name(/ROOT[0]/myproperty)') as [property]
from 
    @xml.nodes('/') as [xml]([node])

Answer

Try this:

DECLARE @XML XML = '<?xml version="1.0" encoding="utf-8"?>
<stns1:testXML...821327282 xmlns:stns1="qaTESTXML">
   <myvalue>Test value</myvalue>
   <myattribute>Test attribute</myattribute>
   <myproperty>Test property</myproperty>
</stns1:testXML...821327282>'

-- you need to **include** and respect the XML namespace in your XML document!    
;WITH XMLNAMESPACES('qaTESTXML' AS ns1)
SELECT
    [node].value('(myvalue)[1]', 'varchar(50)') as [value],
    [node].value('(myattribute)[1]', 'varchar(50)') as [attribute],
    [node].value('(myproperty)[1]', 'varchar(50)') as [property]
FROM   
    @xml.nodes('/ns1:testXML...821327282') as [xml]([node])

Not sure what you mean by selecting the testXML...821327282 value – that’s a XML element / node – not a value…..

Update: if you need to get the “name” of the root node element – you can use this – BE AWARE that using the //myvalue approach is very bad for query performance – especially on larger XML documents! You’ve been warned!

;WITH XMLNAMESPACES('qaTESTXML' AS ns1)
SELECT
    [node].value('local-name(.)[1]', 'varchar(50)') as rootnode,
    [node].value('(//myvalue)[1]', 'varchar(50)') as [value],
    [node].value('(//myattribute)[1]', 'varchar(50)') as [attribute],
    [node].value('(//myproperty)[1]', 'varchar(50)') as [property]
FROM   
    @xml.nodes('/ns1:*') as [xml]([node])