0

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
Community
  • 1
  • 1
CrnaStena
  • 3,017
  • 5
  • 30
  • 48

1 Answers1

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.

CrnaStena
  • 3,017
  • 5
  • 30
  • 48