CODEWITHSUNDEEP
sql-serverxml
tbone
tbone

Reputation: 5865

Possible to extract the *distinct* keys (across all rows) stored within XML in a column?

I have a table [users] with columns: 

[id] int  
[userdetails] nvarchar(max)

[userdetails] 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="tdehdgthrth"/>
    <entry key="another1" value="rthrth"/>
    <entry key="another7" value="etdsryhntdhetdyh"/>
    <entry key="anotherWhatever" value="6546544"/>
  </Map>
</Attributes>

Each row can contain a different combination of count of key/value elements.

In a query I would like to extract a list of all the distinct (across all rows) KEY values. Is this even possible?

I had earlier asked a similar question:

https://stackoverflow.com/a/50281264/8678

The syntax for querying specific elements from the above is like this:

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)

The obvious problem with this is that it requires one to know the available values and hardcode them within the SQL, whereas what I'm trying to do here is discover what the distinct set of possible key values is.

Upvotes: 3

Views: 429

Answers (1)

bzier
bzier

Reputation: 445

In a query I would like to extract a list of all the distinct (across all rows) KEY values. Is this even possible?

What I'm trying to do here is discover what the distinct set of possible key values is.

This will produce the list of distinct keys from all rows (Note: I'm not sure how long each key value may be, but this will only grab 100 characters; change as necessary):

;WITH tbl AS (
    SELECT id, cast(userdetails as xml) ud
    FROM testSo)
SELECT DISTINCT
    t.v.value('(@key)[1]','nvarchar(100)') anEntryKey
FROM tbl 
CROSS APPLY ud.nodes('//entry') t(v)

If you need to get more information about where each came from (which ID), you can get the (non-distinct) list like this:

;WITH tbl AS (
    SELECT id, cast(userdetails as xml) ud
    FROM testSo)
SELECT
    id,
    t.v.value('(@key)[1]','nvarchar(100)') anEntryKey
FROM tbl 
CROSS APPLY ud.nodes('//entry') t(v)

I tested these on SQL Fiddle here using SQL Server 2017.

Upvotes: 2

Related Questions