Select XML nodes as attributes

Question

I am assuming the answer to my question is going to be something simple which I can't figure out myself. Here is the scenario:

I am using SQL Server 2008 R2 where a table has an XML column where the data is saved in the following format:

<Person>
    <firstName>John</firstName>
    <lastName>Lewis</lastName>
</Person>

The Person node can have any number of child-nodes for which the element names might be different (not known beforehand). I am looking for a query to return an XML which has the values for all the nodes as attributes.

So the output for the above XML should be:

<Person firstName="John" lastName="Lewis"/>

I can't think of a query to get the above output. I don't want to use a query like

Select 
      PersonColumn.value('(/Person/firstName)[1]', 'varchar(100)') AS '@firstName'
    , PersonColumn.value('(/Person/lastName)[1]', 'varchar(100)') AS '@lastName'
FROM MyTable
WHERE MyTable.MyPrimaryKey=1
FOR XML PATH('Person'), TYPE

since I don't know what nodes might there be under the Person node.

You say "*The Person node can have any number of descendants for which the names might be different*". Please provide an example of this (multiple descendants with different names), and what you expect as a result. The single-descendant example you gave isn't helpful. — RBarryYoung, Nov 21 '12 at 15:17
What i meant was the Person node might not just have firstName and lastName. It can have more nodes under it but none of the nodes will repeat, viz., firstName will come up only once. I hope that made it more clear. The word Descendant might have confused you. My example shows 2 Descendants to Person which are firstName and lastName. Maybe the more appropriate term would have been Child Nodes. — Wiz, Nov 21 '12 at 15:19
This would be trivial with a CLR (C#) stored proc. Is that an option? — Joshua Honig, Nov 21 '12 at 18:00
See also http://stackoverflow.com/questions/1580077/how-do-i-pivot-on-an-xml-columns-attributes-in-t-sql?rq=1 — Joshua Honig, Nov 21 '12 at 18:04

Roman Pekar · Accepted Answer · 2012-11-21T17:48:55.813

2

I've tried to do this

select
    PersonColumn.query('
        element Person {
            for $i in /Person/*
            return attribute {local-name($i)} {string($i)}
        }
    ')
from MyTable

but it turns out that it's impossible to use dynamic attribute names

XQuery [MyTable.PersonColumn.query()]: Only constant expressions are supported for the name expression of computed element and attribute constructors.: select PersonColumn.query(' element Person { for $i in /Person/* return attribute {local-name($i)} {string($i)} } ') from MyTable

So best I can do so far is

select 
    cast(
        '<Person ' + 
        (
            select
                PersonColumn.query('
                for $i in /Person/*
                return concat(local-name($i), "=""", data($i), """")
                ').value('.', 'nvarchar(max)')
            for xml path('')
        ) + '/>'
    as xml)
from MyTable

It's also possible to do this

select
    cast(
        '<Person ' + 
        PersonColumn.query('
            for $i in /Person/*
            return concat(local-name($i), "=""", data($i), """")
        ').value('.', 'nvarchar(max)') +
        '/>'
      as xml)
from MyTable

but it will not work if your data contains some characters like < > and so on

edited Nov 21 '12 at 17:48

answered Nov 21 '12 at 17:13

Roman Pekar

107,110
28
195
197

1

+1: your "best so far" is actually pretty darn good. One of my favorites, ever. – RBarryYoung Nov 21 '12 at 18:27
This seems pretty close. I will give it a try. Thanks – Wiz Nov 22 '12 at 10:26
This worked great. For the scenario I am using this, there wont be any `<` or `>`. Thank You. – Wiz Nov 22 '12 at 11:24
@Roman: Do you have a link where i can refer to the various things i can use in `PersonColumn.query`? I just realized its not just `<` and `>` where it breaks but even for `&` so need to know is there anything which will read the raw xml than convert `&` to `&` which is happening currently with `data($i)` – Wiz Nov 22 '12 at 11:46
that thing inside query is called XQuery, you can read about it here http://msdn.microsoft.com/en-us/library/ms189075.aspx – Roman Pekar Nov 22 '12 at 12:04

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

It looks like you essentially want to PIVOT on the names of elements that are direct children of the Person node. If you must do this all in TSQL you must construct the query dynamically. See How do I Pivot on an XML column's attributes in T-SQL for inspiration. In that case the pivot expression was also an attribute value, while in your case it's an element name.

I do feel obliged to point out the obvious -- that such a solution is likely to perform poorly, the results be difficult to consume (since column names and count are unknown) and may be susceptible to sql injection attacks depending on the context.

edited May 23 '17 at 12:18

Community

1
1

answered Nov 21 '12 at 18:10

Joshua Honig

12,925
8
53
75

1

A normal PIVOT cannot do this, it could only be done with a dynamic PIVOT. Considering that you would still have to shred it out from XML to a SQL virtual table, do the PIVOT, and then re-compose it back into XML, there seems to be too much hand-waving here without actual examples. – RBarryYoung Nov 21 '12 at 18:26

Select XML nodes as attributes

2 Answers2