CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2012
Tedo G.
Tedo G.

Reputation: 1565

Concat xml node values in MS SQL

I need to get the childnode values of users with multiple keys, as shown below: 

<Individuals>
    <User key="0">
        <UserlID>100</UserlID>
        <FirstName>John Doe</FirstName>
    </User>
    <User key="1">
        <UserlID>101</UserlID>
        <FirstName>Jane Doe</FirstName>
    </User>
    <User key="2">
        <UserlID>102</UserlID>
        <FirstName>Jack Black</FirstName>
    </User>
</Individuals>

My desired result will be either, three rows of all key-s UserID-s or one row concatenating all three key UserID-s. the number of the key is variable, maybe more than three.

Have managed to get static UserID values:

SELECT
   [UserID] = A.XmlField.value('(Individuals/User[@key=2]/UserlID)[1]', 'Int')
FROM [MyTable] As A

but the problem is I cannot get all the userID-s

Upvotes: 2

Views: 880

Answers (1)

Michal C
Michal C

Reputation: 181

You can do it this way:

SELECT u.value('UserlID[1]', 'int') as UserlID
  FROM MyTable CROSS APPLY XmlField.nodes('/Individuals/User') i(u)

Upvotes: 1

Related Questions