Parsing XML data in SQL with attribute-CodePudding

I currently have an XML I am pulling from a column in SQL formatted like this:

<Datas>
   <CHILD value="test" />
</Datas>

I need a way to grab the value in the CHILD tag but I am having some issues I believe to be stemming from it being a self closing tag. I am able to get the data if it was formatted in this format:

<Datas>
   <CHILD>test</CHILD>
</Datas>

However, this format is not an option as the application I'm pulling from does not store it this way.

I have tried the following SQL:

select cast(xmlField as xml) xmlField into tmp from (
select '<Datas><CHILD value = "test"/></Datas>' xmlField
) tb
SELECT
xmlField.value('(Datas/CHILD)[1]', 'nvarchar(max)') as Data
FROM tmp
drop table tmp

Any help is greatly appreciated. Thanks!

CodePudding user response：

The problem isn't that it's self closing, it's that you want to access an attribute. For those you need to use the attribute's name prefixed with an @:

SELECT xmlField.value('(Datas/CHILD/@value)[1]', 'nvarchar(max)') AS Data
FROM tmp;