Convert elements to attributes for XML


Suppose you have the following element centric xml document. You want to change it to attribute centric. How can we do it by SQL Server xquery?

I didn’t find a straight forward way to achieve this by xquery. The best I found is to build a string of the attribute centric xml by xquery and convert it to xml.


declare @x xml,@y xml,@new xml,@z nvarchar(max);









SELECT @y=@x.query(

for $r in root/*

return <row> {local-name($r)}

{for $c in $r/*

return concat(” ”,local-name($c),”="”,string(data($c)),”"”)}



select @z=N‘<root>’




@y.nodes(‘/row’) x(r)




@new=cast(@z as xml)



/* Output is


<Table1 Field1="111" Field2="112" />

<Table1 Field1="121" Field2="122" />

<Table2 Field1="211" Field2="212" />

<Table2 Field1="221" Field2="222" />




The query uses the "for" and "return" clause of xquery FLWER command. The first "for" clause enumerate all the rows in the source xml. It creates the node <row> and add the table name as part of the node value, i.e. return <row> {local-name($r)}

The nested "for" clause enumerate all elements (columns) in one row and concatenate the column (element) name and value in the form of xml attribute and output it as the <row> node value of the outer "for" caluse.


This entry was posted in XML, XQuery and XPath. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s