Looping though XML Subnode and pass the Identity value

Question

Post reply

Looping though XML Subnode and pass the Identity value

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 1

Hi LutzM

I tried to do it. But i don't know its giving me error.

Below is the XML File ,,I am dealing with.

'<?xml version="1.0" ?>

</Ridge>

<InsertUserID>ASDZXCCFG</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<InsertUserID>Child1</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<Kids>

</Kid>

</Kid>

</Kids>

</Child>

<InsertUserID>Child2</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<Kids>

</Kid>

</Kids>

</Child>

</Childs>

</Parent>

<InsertUserID>GHTRDF</InsertUserID>

<UpdateUserID>ADMIN_USER</UpdateUserID>

<InsertUserID>Child9</InsertUserID>

<UpdateUserID>DELUX_USER</UpdateUserID>

</Child>

</Childs>

</Parent>

</Parents>

</Mains>'

LutzM SSC Guru Points: 107049 More actions · Answer 2

see item b in my previous post.

Side note: It usually helps if the error code is included in the post, not just mentioned...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 3

Below is the code I am trying,,,I chaged the XML file,,might be something missing in that.

I am getting the below error

--Create Parent Table

CREATE TABLE [dbo].[Parent]

(

[Parent_ID] [int] IDENTITY(1,1) primary key clustered,

[InternalID] [int] NULL,

[Action] [nvarchar](255) NULL,

[InsertUserID] [nvarchar](255) NULL,

[InsertDateTime] [nvarchar](255) NULL,

[UpdateUserID] [nvarchar](255) NULL

)

--Create Child Table

CREATE TABLE [dbo].[Child]

(

[Child_ID] [int] IDENTITY(1,1) primary key clustered,

[Parent_ID] int references Parent(Parent_ID),

[InternalID] [int] NULL,

[Action] [nvarchar](255) NULL,

[InsertUserID] [nvarchar](255) NULL,

[InsertDateTime] [nvarchar](255) NULL,

[UpdateUserID] [nvarchar](255) NULL

)

--Create KidTable

CREATE TABLE [dbo].[Kid]

(

[Kid_ID] [int] IDENTITY(1,1) primary key clustered,

[Child_ID] int references Child(Child_ID),

[InternalID] [int] NULL,

[Action] [nvarchar](255) NULL,

[InsertUserID] [nvarchar](255) NULL,

[InsertDateTime] [nvarchar](255) NULL,

[UpdateUserID] [nvarchar](255) NULL

)

DECLARE @xml XML

SELECT @xml=

'<?xml version="1.0" ?>

</Ridge>

<InsertUserID>ASDZXCCFG</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<InsertUserID>Child1</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<Kids>

</Kid>

</Kid>

</Kids>

</Child>

<InsertUserID>Child2</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<Kids>

</Kid>

</Kids>

</Child>

</Childs>

</Parent>

<InsertUserID>GHTRDF</InsertUserID>

<UpdateUserID>ADMIN_USER</UpdateUserID>

<InsertUserID>Child9</InsertUserID>

<UpdateUserID>DELUX_USER</UpdateUserID>

</Child>

</Childs>

</Parent>

</Parents>

</Mains>'

create table #ParentIdReference(Individual_ID INT,InternalID INT)

;

WITH XMLNAMESPACES(

DEFAULT 'http://ALD.riban.com'

)

INSERT

INTO Parent

OUTPUT inserted.Parent_ID, inserted.InternalID

INTO #ParentIdReference

SELECT

c.value('@InternalID[1]','INT') AS InternalID,

c.value('@Action[1]','varchar(10)') AS [ACTION],

c.value('InsertUserID[1]', 'varchar(10)') AS InsertUserID,

c.value('InsertDateTime[1]', 'varchar(10)') AS InsertDateTime,

c.value('UpdateUserID[1]', 'varchar(10)') AS UpdateUserID

FROM @xml.nodes('Mains/Parents/Parent') T(c)

DECLARE @ChildIdReference TABLE (Child_ID INT,Parent_ID INT,InternalID INT)

INSERT

INTO Child

OUTPUT inserted.Child_ID,inserted.Parent_ID, inserted.InternalID

INTO @ChildIdReference

SELECT

p.Parent_ID,

v.value('@InternalID[1]','INT') AS InternalID,

v.value('@Action[1]','varchar(10)') AS [ACTION],

v.value('InsertUserID[1]', 'varchar(10)') AS InsertUserID,

v.value('InsertDateTime[1]', 'varchar(10)') AS InsertDateTime,

v.value('UpdateUserID[1]', 'varchar(10)') AS UpdateUserID

FROM @xml.nodes('Mains/Parents/Parent') T(c)

CROSS APPLY T.c.nodes('Childs/Child') U(v)

CROSS APPLY #ParentIdReference P

WHERE p.internalid=c.value('@InternalID[1]','INT')

INSERT INTO Kid

SELECT

c.child_ID,

x.value('@InternalID[1]','INT') AS InternalID,

x.value('@Action[1]','varchar(10)') AS [ACTION],

x.value('InsertUserID[1]', 'varchar(10)') AS InsertUserID,

x.value('InsertDateTime[1]', 'varchar(10)') AS InsertDateTime,

x.value('UpdateUserID[1]', 'varchar(10)') AS UpdateUserID

FROM @xml.nodes('Mains/Parents/Parent') T(c)

CROSS APPLY T.c.nodes('Childs/Child') U(v)

CROSS APPLY U.v.nodes('Kids/Kid') W(x)

CROSS APPLY @ChildIdReference c

WHERE c.internalid=v.value('@InternalID[1]','INT')

drop table #parentIDReference

LutzM SSC Guru Points: 107049 More actions · Answer 4

errmm... :unsure:

Did you notice what I mentioned as b) two posts before, restated in my previous post?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 5

I appriciated your responce. But I never worked with the XML file Before.

I Mentioned the Mains node in the Query "FROM @xml.nodes('Mains/Parents/Parent')".

I don't know u mean to change it like that or something else.

I created my final database. And at the end i got the file with namespace.

Thanks in advance.

LutzM SSC Guru Points: 107049 More actions · Answer 6

a) the end tag < / Ridge > has no start tag

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 7

I deleted that </Redge> part still its not working.The last updated query which post its don't have that part still i am getting error in loading the child table.

LutzM SSC Guru Points: 107049 More actions · Answer 8

Run the code.

Read the error meassge.

Try to figure out what it means (e.g. column name not defined in the referenced table?)

Try to resolve the issue.

If you can't figure it out, post back.

Strong hint (repeated)

Side note: It usually helps if the error code is included in the post, not just mentioned...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 9

Thanks LutzM ,,I figured out that,,in the ParentIDreference I changed it to Parent_ID instead of Individual_ID. After running the code,,2 rows affected in parent table and 0 rows affected in the child and kid table. I tried to change the path in guery for the nodes,,but not getting any result.

I appriciate your help.

LutzM SSC Guru Points: 107049 More actions · Answer 10

You run multiple queries against the same (namespaced) xml source.

Therefore, you'd need to declare the names pace for each and every query against that xml source. Right now it's just used in the first query...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 11

Hey LutzM,

Thanks Alot,,I got it...its working now,,,

krishusavalia SSCrazy Points: 2385 More actions · Answer 12

Hey Lutz,

Thanks for you help.

I am using the solution with cross apply for keep identity values in child nodes.

But now i am facing difficuilty because now i am getting XML file

which have more than hundred parents records and it has like more than 1000 records for child.

So runnning this code with cross apply,,,its taking more than 3 hrs to load that just one file.

Do you have any solution so I can do it without cross apply?

Thanks in advance.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 13

Hi, I don't have your latest data/query to work with, but expanding on Lutz' excellent code, you could try this approach

DECLARE @ParentIdReference TABLE (Parent_ID INT,InternalID VARCHAR(10))

;WITH XMLNAMESPACES(

DEFAULT 'http://ALD.riban.com'

)

INSERT

INTO Parent

OUTPUT inserted.Parent_ID, inserted.InternalID

INTO @ParentIdReference

SELECT

c.value('@InternalID[1]','varchar(10)') AS InternalID,

c.value('@Action[1]','varchar(10)') AS [ACTION],

c.value('InsertUserID[1]', 'varchar(10)') AS InsertUserID,

c.value('InsertDateTime[1]', 'varchar(10)') AS InsertDateTime,

c.value('UpdateUserID[1]', 'varchar(10)') AS UpdateUserID

FROM @xml.nodes('Mains/Parents/Parent') T(c)

DECLARE @ChildIdReference TABLE (Child_ID INT,Parent_ID INT,InternalID INT)

;WITH XMLNAMESPACES(

DEFAULT 'http://ALD.riban.com'

)

INSERT

INTO Child

OUTPUT inserted.Child_ID,inserted.Parent_ID, inserted.InternalID

INTO @ChildIdReference

SELECT

p.Parent_ID,

v.value('@InternalID[1]','INT') AS InternalID,

v.value('@Action[1]','varchar(10)') AS [ACTION],

v.value('InsertUserID[1]', 'varchar(10)') AS InsertUserID,

v.value('InsertDateTime[1]', 'varchar(10)') AS InsertDateTime,

v.value('UpdateUserID[1]', 'varchar(10)') AS UpdateUserID

FROM @ParentIdReference P

OUTER APPLY @xml.nodes('Mains/Parents/Parent[ @InternalID=sql:column("P.InternalID")]/Childs/Child') U(v)

;WITH XMLNAMESPACES(

DEFAULT 'http://ALD.riban.com'

)

INSERT INTO Kid

SELECT

c.child_ID,

x.value('@InternalID[1]','INT') AS InternalID,

x.value('@Action[1]','varchar(10)') AS [ACTION],

x.value('InsertUserID[1]', 'varchar(10)') AS InsertUserID,

x.value('InsertDateTime[1]', 'varchar(10)') AS InsertDateTime,

x.value('UpdateUserID[1]', 'varchar(10)') AS UpdateUserID

FROM @ChildIdReference c

CROSS APPLY @xml.nodes('//Childs/Child[ @InternalID=sql:column("c.InternalID")]/Kids/Kid') W(x)

The key here being that this method of selecting only the relevant nodes for each parent node by use of the sql:column() function will hopefully reduce the workload...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

LutzM SSC Guru Points: 107049 More actions · Answer 14

Do you still use the XML variable?

If so, you might want o try loading the data into a table with an ID column (being the clustered index) and add an XML index.

This might help...

Question aside: how large is that file? Would you be able to provide the basic structure of the file and your current query?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]