Concat xml node values in MS SQL

Question

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

Michal C · Accepted Answer · 2017-01-16 12:36:12Z

1

You can do it this way:

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

answered Jan 16, 2017 at 12:36

Michal C

1814 bronze badges

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

Collectives™ on Stack Overflow

Concat xml node values in MS SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related