Convert XML with nested tables / elements to regular (sql)Tables
-
Someone provided me an inventory xml (see attached file)
ServerLocalGroupsLocalGroupthegroupname /thegroupnamethegroupdescription /thegroupdescriptionMembersMembername /nametype /type/member.../members/LocalGroupLocalGroupthegroupname /thegroupnamethegroupdescription /thegroupdescriptionMembersMembername /nametype /type/member.../members/LocalGroup.../LocalGroupsLocalSharesanother nested table/LocalShares.../ServerHow to import the xml tables into regular sql tables and retaining the nested characteristics (parent child) ?
I started importing the xmlfile into an xmlcolumn of a temp table.
create table #XmlTable (xmlCol xml)insert into #XmlTable (xmlCol)select BulkColumnfrom openrowset(bulk 'C:\temp\XML\MyServer_Review.xml', single_blob) as dtDeclare @InventoryServerName varchar(128), @TsInventory datetimeSELECT @InventoryServerName = xmlCol.value('(/Server/ServerName)[1]', 'VARCHAR(128)'), @TsInventory = convert(datetime, xmlCol.value('(/Server/Date)[1]', 'VARCHAR(128)'),103) -- dd/mm/yyyyfrom #XmlTableCreate table #LocalAccount ( [ServerName] varchar(128) not null, [TsInventory] datetime not null, [Name] varchar(128) not null Primary key, [Status] varchar(20) not null, [Description] varchar(500) null, [Credentials] varchar(500) null)/*Layout*//* Convert to XML node format */DECLARE @x XMLSELECT @x =( SELECTxmlCol.query('for $a in //LocalAccountreturn <LocalAccountName="{$a/Name}"Status="{$a/Status}"Description="{$a/Description}"Credentials="{$a/Credentials}"/>')FROM #XmlTableFOR XML AUTO)Insert into #LocalAccountSelect @InventoryServerName as InventoryServerName, @TsInventory as TsInventory, T.Item.value('@Name', 'varchar(128)') as Name, T.Item.value('@Status' , 'varchar(20)') as [Status], T.Item.value('@Description' , 'varchar(500)') as [Description], T.Item.value('@Credentials', 'varchar(128)') as [Credentials]FROM @x.nodes('//LocalAccount') AS T(Item)Select *from #LocalAccountorder by [Name]The problem is I don't know how to handle the nested elements so their relationship is still OK.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The magic trick is to shred it into one big flattened table using the CROSS APPLY operator.
For example (code snippit):
SELECTx1.stage.value('@ID', 'uniqueidentifier'),x2.activity.value('@ID', 'uniqueidentifier'),x2.activity.value('@Ordinal', 'int'),x2.activity.value('@ActivityTemplateID', 'uniqueidentifier')FROM@p_xml.nodes('/MyRootNode/Stages/Stage') AS x1(stage)CROSS APPLY x1.stage.nodes('ActivityRefs/ActivityRef') AS x2(activity)Where:
@p_xml (datatype xml) is the parameter to my stored procedure.
Sometimes it help to code this as a CTE (i.e., the WITH clause) to make it a bit more managable.
Sorry, I don't have an independent example. I think BOL also has an example of the above.
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
Thank you for your help.
I keep on getting lost whenever xml is being used with cross apply.
How does it retain the correct parent/child dependency ?
I'll try to work on this tomorrow.
I'll keep you informed.
Johan
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
See: Jacob Sebastian's Sales Order Workshop Part IV
http://www.sqlservercentral.com/articles/Stored+Procedures/2912/
BTW, Don't use OPENXML. Use XQuery
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
Thanks again
It gets quit confusing (sheer volume) to find the right corner in the maze to get started. :hehe:
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
It took me a lot of frustrations but I got it to work :w00t:
-- ...If object_id('tempdb..#LocalGroupMembers') IS NULLbeginCreate table #LocalGroupMembers ( IdNr int identity(1,1) not null, [ServerName] varchar(128) not null, [TsInventory] datetime not null, [GroupName] varchar(128) not null, [MemberName] varchar(128) not null, [Type] varchar(25) not null)endelsebegintruncate table #LocalGroupMembersendInsert into #LocalGroupMembers ( [ServerName], [TsInventory], [GroupName], [MemberName], [Type] )SELECT @InventoryServerName as InventoryServerName, @TsInventory as TsInventory, R.xmlMember.value('../../Name[1]','varchar(128)') GroupName, R.xmlMember.value('./Name[1]', 'varchar(128)') MemberName, R.xmlMember.value('./Type[1]', 'varchar(128)') MemberTypeFROM #XmlTable TCROSS APPLY xmlCol.nodes('//LocalGroup/Members/Member') AS R(xmlMember)Select *from #LocalGroupMembersorder by IdNr-- ...Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Here is another version of the code that does not use the 'parent node accessor'
DECLARE @t TABLE (data XML)INSERT INTO @t(data) SELECT '[LocalGroups][LocalGroup][Name]Administrators[/Name][Description]Administrators have complete and ...[/Description][Members][Member][Name]WS20098002/Administrator[/Name][Type]User[/Type][/Member][Member][Name]ARMONY/Domain Admins[/Name][Type]Group[/Type][/Member][/Members][/LocalGroup][LocalGroup][Name]Backup Operators[/Name][Description]Backup Operators can override ...[/Description][Members /][/LocalGroup][/LocalGroups]'SELECTg.value('Name[1]','varchar(25)') GroupName, R.xmlMember.value('Name[1]', 'varchar(25)') MemberName, R.xmlMember.value('Type[1]', 'varchar(25)') MemberTypeFROM @t TCROSS APPLY data.nodes('//LocalGroup') AS l(g)CROSS APPLY g.nodes('Members/Member') AS R(xmlMember)/*GroupName MemberName MemberType------------------------- ------------------------- -------------------------Administrators WS20098002/Administrator UserAdministrators ARMONY/Domain Admins Group*/.
-
Thank you Jacob.
I'll give it a try.
Johan
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I got things working a little bit :crazy:
I have attached the sample data and the needed queries.
The problem is the comparison query never ends ....
This server has +/- 1400 shares ( don't ask why :hehe: ) and finding the differences has the xml querying variant running away ...
/* XML query takes forever ! */Select @ServerName as ServerName, Coalesce(OLD.OLS.value('Name[1]', 'varchar(128)'),NEW.NLS.value('Name[1]', 'varchar(128)')) ShareName, @OLDTsInventory, OLD.OLS.value('Path[1]', 'varchar(500)') as [OLDPath], @NEWTsInventory as NEWTsInventory, NEW.NLS.value('Path[1]', 'varchar(500)') as [NEWPath]FROM @OLDInventory.nodes('//LocalShare') AS OLD ( OLS )FULL OUTER JOIN @NEWInventory.nodes('//LocalShare') AS NEW ( NLS )on OLD.OLS.value('Name[1]', 'varchar(128)') = NEW.NLS.value('Name[1]', 'varchar(128)')Where not ( OLD.OLS.value('Path[1]', 'varchar(500)') = NEW.NLS.value('Path[1]', 'varchar(500)') )or OLD.OLS.value('Name[1]', 'varchar(128)') is nullor NEW.NLS.value('Name[1]', 'varchar(128)') is nullorder by ShareNameAnyone know what I'm messing up ??
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Never mind, I created a carthesian resultset because I didn't include all elements to be compared in the ON-clause of the join.
Now it's working a bit better, but still doesn't match the nested table expression version.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply