March 18, 2009 at 4:08 am
Someone provided me an inventory xml (see attached file)
Server
LocalGroups
LocalGroup
thegroupname /thegroupname
thegroupdescription /thegroupdescription
Members
Member
name /name
type /type
/member
...
/members
/LocalGroup
LocalGroup
thegroupname /thegroupname
thegroupdescription /thegroupdescription
Members
Member
name /name
type /type
/member
...
/members
/LocalGroup
...
/LocalGroups
LocalShares
another nested table
/LocalShares
...
/Server
How 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 BulkColumn
from openrowset(bulk 'C:\temp\XML\MyServer_Review.xml', single_blob) as dt
Declare @InventoryServerName varchar(128)
, @TsInventory datetime
SELECT @InventoryServerName = xmlCol.value('(/Server/ServerName)[1]', 'VARCHAR(128)')
, @TsInventory = convert(datetime, xmlCol.value('(/Server/Date)[1]', 'VARCHAR(128)'),103) -- dd/mm/yyyy
from #XmlTable
Create 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 XML
SELECT @x =
( SELECT
xmlCol.query('
for $a in //LocalAccount
return <LocalAccount
Name="{$a/Name}"
Status="{$a/Status}"
Description="{$a/Description}"
Credentials="{$a/Credentials}"
/>
')
FROM #XmlTable
FOR XML AUTO
)
Insert into #LocalAccount
Select @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 #LocalAccount
order 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
March 19, 2009 at 1:48 pm
The magic trick is to shred it into one big flattened table using the CROSS APPLY operator.
For example (code snippit):
SELECT
x1.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.
March 19, 2009 at 2:48 pm
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
March 19, 2009 at 2:58 pm
See: Jacob Sebastian's Sales Order Workshop Part IV
http://www.sqlservercentral.com/articles/Stored+Procedures/2912/
BTW, Don't use OPENXML. Use XQuery
March 20, 2009 at 1:08 am
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
March 20, 2009 at 7:36 am
It took me a lot of frustrations but I got it to work :w00t:
-- ...
If object_id('tempdb..#LocalGroupMembers') IS NULL
begin
Create 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
)
end
else
begin
truncate table #LocalGroupMembers
end
Insert 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)') MemberType
FROM #XmlTable T
CROSS APPLY xmlCol.nodes('//LocalGroup/Members/Member') AS R(xmlMember)
Select *
from #LocalGroupMembers
order 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
March 20, 2009 at 2:41 pm
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]'
SELECT
g.value('Name[1]','varchar(25)') GroupName
, R.xmlMember.value('Name[1]', 'varchar(25)') MemberName
, R.xmlMember.value('Type[1]', 'varchar(25)') MemberType
FROM @t T
CROSS APPLY data.nodes('//LocalGroup') AS l(g)
CROSS APPLY g.nodes('Members/Member') AS R(xmlMember)
/*
GroupName MemberName MemberType
------------------------- ------------------------- -------------------------
Administrators WS20098002/Administrator User
Administrators ARMONY/Domain Admins Group
*/
.
March 23, 2009 at 1:37 am
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
March 24, 2009 at 1:56 am
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 null
or NEW.NLS.value('Name[1]', 'varchar(128)') is null
order by ShareName
Anyone 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
March 24, 2009 at 6:38 am
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply