June 6, 2012 at 7:26 am
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.
June 6, 2012 at 9:28 am
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.
June 7, 2012 at 4:36 am
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
June 7, 2012 at 5:05 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply