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);

set

@x=N

<root>

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

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

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

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

</root>

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)),”"”)}

</row>

)

select @z=N‘<root>’

SELECT

@z=@z+‘<‘+x.r.value(‘.’,‘nvarchar(max)’)+‘/>’

FROM

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

SELECT

@z=@z+‘</root>’

SELECT

@new=cast(@z as xml)

SELECT

@new

/* Output is

<root>

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

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

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

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

</root>

*/

/*

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.

*/

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s