SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to add a specific new attribute in all elements of every node in any given xml


How to add a specific new attribute in all elements of every node in any given xml

Author
Message
iBar
iBar
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 842
Hi,

In a provided xml either in @variable or xml column of a table, how to add a specific new attribute e.g. {new_attribute="1"} in every child element of given xml.

The path in method modify(insert) must be variable and must not be hard coded based on input xml.

Any possibility please?

Thanks

For example
Input1:


<books>
<book>
<author>
<name />
<age />
<title />
</book>
</books>





Output1:

<books>
<book new_attribute="1">
<author new_attribute="1">
<name new_attribute="1" />
<age new_attribute="1" />
</author>
<title/>
</book>
</books>




Input2:

<university>

<department>
<computer>
<address />
</computer>
<maths>
<address />
</maths>
</department>

<library>
<computer>
<address />
</computer>
<maths>
<address />
</maths>
</library>

</university>



Output2:


<university new_attribute="1">

<department new_attribute="1">
<computer new_attribute="1">
<address new_attribute="1" />
</computer>
<maths new_attribute="1">
<address new_attribute="1"/>
</maths>
</department>

<library new_attribute="1">
<computer new_attribute="1">
<address new_attribute="1" />
</computer>
<maths new_attribute="1">
<address new_attribute="1" />
</maths>
</library>

</university>




Note: i just mocked these example for clarity and understanding. Data logic can be wrong but that is irrelevant.

Thanks.
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
Unfortunately, using tsql, it isn't possible (easily that is) to add an attribute into every child node as the target of an insert operation needs to be a single node.

Personally, if I had a similar requirement to add an attribute into all child nodes of any given/unknown xml structure, then I would probably look at using an xslt to do a transform on the xml rather than tsql. Otherwise to try and achieve this in tsql, you are going to have to come up with a pretty complex dynamic based SQL script that reads the structure of the xml, shred it in some part and then perform lots of dynamic xml modify operations against the xml structure to add the attributes to the elements.

I did blog about xml dml basics http://www.olcot.co.uk/sql-blogs/xml-dml---using-the-modify-method-part-1-delete and there is a part on adding attributes to elements, but i'm guessing this isn't going to help you here.

If you know what the xml structure is before hand then it may not be so bad to shred it to a flat table and then regenerate it with the attributes, but without knowing how big/complex your xml structures are, i can't really advise on that as a potential way forward. Also, if you don't know what the xml structures are going to be at runtime, then this isn't going to be an option.
iBar
iBar
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 842
Thanks arthurolcot,

The idea is to take a certain DML action on all nodes, based on the newly added attribute and according to the requirement at a particular time.

I tried different things to make it dynamic but i always found a new level of complexity.

That was the purpose of this question.

Still i will keep on searching and If i will be able to construct something more generic then i will share.

Thanks for your valuable comments.

Cheers
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
No problem.

I did spend a few minutes today and came up with the following rather simplistic/crude script which may offer some kind of option for you. I don't really like this personally and it hasn't been tested at all (other than the one example) so feel free to do with it as you wish. I don't know how it will handle large or deep xml structures either. It does rely on another function called XMLTable which is blogged here: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx which you'll need first.

The idea of this script is that it breaks down the xml to a flat table and then reconstructs the xml back again whilst adding in an new attribute into every node.


DECLARE @xml XML
SET @xml = '<books>
<book>
<author>
<name>aaaaa</name>
<age>12</age>
<title>mr</title>
</author>
</book>
</books>'

--declare variable to hold column information and set to empty string
DECLARE @cols VARCHAR(MAX)
SET @cols = ''

--create string of all columns in the xml and set the column alias to the fullpath in the xml
--add in the new attribute as part of the columns
--use the xmltable function from Jacob Sebastian
SELECT @cols = @cols + '''1'' as ''' + FullPath + '/@new_attribute'', ''' + ISNULL(Value, '') + ''' as ''' + FullPath + ''','
FROM dbo.XMLTable(@xml)

--declare sql variable and construct the dynamic sql statement with the columns
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'select ' + LEFT(@cols, LEN(@cols) - 1) + ' FOR XML PATH('''')'

--execute
EXEC sys.sp_executesql @SQL


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search