Parsing OpenXML with multiple elements of the same name based on attribute

Question

Similar to this question but I need to parse based on specific attribute. How do you select only one of the elements with the same name based on the attribute value?

DECLARE @doc int
EXEC sp_xml_preparedocument @doc OUTPUT, N'
<rootnode>
  <group>
    <id>1</id>
     <anothernode lang="de">first string</anothernode>
     <anothernode lang="en">second string</anothernode>
  </group>
  <group>
    <id>1</id>
     <anothernode lang="en">I</anothernode>
     <anothernode lang="de">Ich</anothernode>
  </group>
</rootnode>'

SELECT *
FROM OPENXML (@doc, 'rootnode/group')
WITH
(
    id int 'id',
    anothernode  varchar(30) 'anothernode'
)

EXEC sp_xml_removedocument @doc

Result is:

1   first string
2   Ich

And I need:

1   second string
2   I

CrnaStena · Accepted Answer · 2013-01-16 22:13:42Z

2

After some hacking I found that I can get correct result by doing following SELECT:

SELECT *
FROM OPENXML (@doc, 'rootnode/group')
WITH
(
    id int 'id',
    anothernode  varchar(30) 'anothernode[@lang="en"]'
)

Result is:

1   second string
2   I

Main difference is [@lang="en"], which selects specific attribute value.

edited Jan 16, 2013 at 22:13

answered Jan 16, 2013 at 19:53

CrnaStena

3,2355 gold badges33 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Parsing OpenXML with multiple elements of the same name based on attribute

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related