CODEWITHSUNDEEP
sql-serverxml
tbone
tbone

Reputation: 5865

How to extract XML elements stored in a column of type NVARCHAR into seperate columns

Scenario: in SQL Server, I have a table [users] with columns [id] int and userdetails nvarchar(max) which contains data like:

<Attributes>
  <Map>
    <entry key="displayName" value="Administrator"/>
    <entry key="email" value="[email protected]"/>
    <entry key="firstname" value="Joe"/>
    <entry key="lastname" value="Blow"/>
  </Map>
</Attributes>

In a query I'd would like to extract as individual columns, like so:

displayName | email | firstname | lastname

What syntax could I use to accomplish this? (preferably in a performant manner as I have lots of queries to perform against potentially large tables....but even getting it working at all would be lovely)

What I've tried so far: 2+ hours of googling and trying various different syntax with no success (failure due to a variety of error messages, the resolution of each also being unsuccessful).

A secondary question might be whether there is a way to accommodate possibly missing key values in some rows (ie: email may not be specified, at all)

Upvotes: 0

Views: 174

Answers (1)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

You can use SQL XML functions and XPath query. This is an example.

declare @tbl table(id int, userdetails nvarchar(max))
insert @tbl(id,userdetails)
values(1,'<Attributes>
  <Map>
    <entry key="displayName" value="Administrator"/>
    <entry key="email" value="[email protected]"/>
    <entry key="firstname" value="Joe"/>
    <entry key="lastname" value="Blow"/>
  </Map>
</Attributes>')

;with tbl as (
select id,cast(userdetails as xml) ud
from @tbl)
select id,
t.v.value('entry[@key="displayName"][1]/@value','nvarchar(100)') displayName,
t.v.value('entry[@key="email"][1]/@value','nvarchar(100)') email,
t.v.value('entry[@key="firstname"][1]/@value','nvarchar(100)') firstname,
t.v.value('entry[@key="lastname"][1]/@value','nvarchar(100)') lastname
from tbl 
cross apply ud.nodes('Attributes/Map') t(v)

Upvotes: 3

Related Questions