Parsing XML data in SQL with self closing tag-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 a property. For those you need to use the property's name prefixed with an @:

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