Parsing XML data in SQL with attribute

Question

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!

Charlieface · Accepted Answer · 2021-10-13 05:45:12Z

2

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;

edited Oct 13, 2021 at 5:45

Charlieface

78.9k8 gold badges35 silver badges77 bronze badges

answered Oct 12, 2021 at 19:55

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marc Pfister Over a year ago

Thank you so much!

Collectives™ on Stack Overflow

Parsing XML data in SQL with attribute

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related