Read XML node attributes without using node name in SQL Server query

Question

I have XML stored into database with the different tag names but the same attribute names:

<book Category="Hobbies And Interests" PropertyName="C#" CategoryID="44" />
<sport Category="Hobbies And Interests" PropertyName="Cricket" CategoryID="46" />

These are just two examples but the tag name can be anything. I want to read "PropertyName" attribute from all node.

Is it possible? If yes then please anyone guide me.

i-one · Accepted Answer · 2013-07-26 13:26:54Z

5

declare @xml xml

set @xml = '<book Category="Hobbies And Interests" PropertyName="C#" CategoryID="44" />
<sport Category="Hobbies And Interests" PropertyName="Cricket" CategoryID="46" />'

select T.c.value('@PropertyName', 'varchar(100)')
from @xml.nodes('/*') T(c)

If you expect that there can be elements without PropertyName attribute, you can use:

select T.c.value('@PropertyName', 'varchar(100)')
from @xml.nodes('/*[@PropertyName]') T(c)

If you also expect that elements can be nested, you can use:

select T.c.value('@PropertyName', 'varchar(100)')
from @xml.nodes('//*[@PropertyName]') T(c)

edited Jul 26, 2013 at 13:26

answered Jul 25, 2013 at 12:08

i-one

5,1201 gold badge33 silver badges44 bronze badges

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

3 Comments

Bogdan Sahlean Over a year ago

@i-one: look at this query

DECLARE @x XML; SET @x=N'<book Category="Hobbies And Interests" CategoryID="44"><chapter PropertyName="111" /></book><sport Category="Hobbies And Interests" CategoryID="46" />'; SELECT T.c.value('@PropertyName', 'VARCHAR(100)') FROM @x.nodes('/*') T(c);

that returns only NULLs thought there is a PropertyName attribute.

i-one Over a year ago

@BogdanSahlean, you right. But in the original data there are no nested elements, and that was the assumption. If necessary it can be generalized by changing .nodes('/*') to .nodes('//*[@PropertyName]'). In any case I have no influence on accept marks.

Bogdan Sahlean Over a year ago

I agree that the test data provided hasn't nested elements. My point is that OP "want[s] to read "PropertyName" attribute from all node[s].".

Collectives™ on Stack Overflow

Read XML node attributes without using node name in SQL Server query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related