Looping though XML Subnode and pass the Identity value

Question

Looping though XML Subnode and pass the Identity value

krishusavalia

SSCrazy

Points: 2385
More actions
December 14, 2010 at 2:22 pm

#104018

--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 @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
'<Parent InternalID="154805" Action="Update">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>08-29-2008 17:13:57</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>13-05-2010 13:22:41</UpdateDateTime>
<Childs>
<Child InternalID="4926250" Action="Update">
<InsertUserID>Child1</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>11-05-2009 15:22:44</UpdateDateTime>
<Kids>
<Kid InternalID="3406436" Action="Insert">
<InsertUserID>Kid1</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>CATS</UpdateUserID>
<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>
</Kid>
<Kid InternalID="3406437" Action="Update">
<InsertUserID>KId2</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>CATS</UpdateUserID>
<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>
</Kid>
</Kids>
</Child>
<Child InternalID="4926251" Action="Update">
<InsertUserID>Child2</InsertUserID>
<InsertDateTime>08-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>11-05-2009 15:22:44</UpdateDateTime>
<Kids>
<Kid InternalID="3406438" Action="None">
<InsertUserID>Kid3</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>CATS</UpdateUserID>
<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>
</Kid>
</Kids>
</Child>
</Childs>
</Parent>'
-----Insert into Parent Table
Insert into Parent
SELECT *
FROM OPENXML(@hDoc, '/Parent',3)
WITH (
[InternalID] [int],
[Action] varchar(10),
[InsertUserID] varchar(10),
[InsertDateTime] varchar(10),
[UpdateUserID] varchar(10))
----------------------------
Declare @Parent_ID INT
SELECT @Parent_ID = @@IDENTITY
-------------------------
--Insert into Child table
Insert into Child
select @Parent_ID,*
FROM OPENXML(@hDoc, '/Parent/Childs/Child',3)
WITH (
[InternalID] [int],
[Action] varchar(10),
[InsertUserID] varchar(10),
[InsertDateTime] varchar(10),
[UpdateUserID] varchar(10))
declare @Child_ID int
select @Child_ID = @@identity
--Insert into Request table
Insert into Kid
select @Child_ID,*
FROM OPENXML(@hDoc, '/Parent/Childs/Child/Kids/Kid',3)
WITH (
[InternalID] [int],
[Action] varchar(10),
[InsertUserID] varchar(10),
[InsertDateTime] varchar(10),
[UpdateUserID] varchar(10))
------------------------------------------------------
select * from Parent
select * from Child
select * from Kid
I am trying to load the XML data into SQL server table.
Below is the structure for my table I have Identity column in each table and I want to pass that Identity value to the subnode. But when I am trying to load the third level(Kid table) , I am just getting the last Identity value from the Child Table into the Kid table.
I need Child_ID 1 for InsertUserID(Kid1,Kid2) and Child_ID 2 for InsertUserID(Kid3) in the Child Table.
Is there any way that i can get the populate the one row in Child table and get the identity of that and populate the Kid table associate with that Child_ID , and again same for the second row in the Child table.
---Below is the Expected result for the Kid Table
Kid_ID Child_ID InternalID Action InsertUserID InsertDateTime UpdateUserID
1 1 3406436 Insert Kid1 02-20-2008 CATS
2 1 3406437 Update KId2 02-20-2008 CATS
3 2 3406438 None Kid3 02-20-2008 CATS
--But I am getting the this:(Child_ID 2 for all the records in Kid Table)
Kid_ID Child_ID InternalID Action InsertUserID InsertDateTime UpdateUserID
1 2 3406436 Insert Kid1 02-20-2008 CATS
2 2 3406437 Update KId2 02-20-2008 CATS
3 2 3406438 None Kid3 02-20-2008 CATS

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Will it ever happen that the source file could have data with identical Parent InternalID's, Child InternalID's or Kid InternalID's repeated within different nodes?

Something like

Parent InternalID="154805"

__Child InternalID="4926250"

____Kid InternalID="3406436"

__Child InternalID="xyz"

____Kid InternalID="3406436"

Parent InternalID="abc"

__Child InternalID="4926250"

____Kid InternalID="3406436"

As a side note: I would redesign the concept to use XQuery instead of Openxml...

It's most probably faster in your scenario since it's required to reference nodes from one/two levels up....

Let me know if the scenario described above might happen...

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 2

Thanks for your fast reply,,,but InternalID is defferent in all the tables.

I am facing the difficulty into pass the identitly value of Child table to the appropriate Kid values.

LutzM SSC Guru Points: 107049 More actions · Answer 3

krishusavalia (12/14/2010)
Thanks for your fast reply,,,but InternalID is defferent in all the tables.
I am facing the difficulty into pass the identitly value of Child table to the appropriate Kid values.

Which InternalID do you refer to (it's used in every node)? Also, I asked regarding duplicates within your xml structure.

The reason for asking: I would use the output clause for each insert statement and copy the assigned ID as well as the original InternalID into separate tables. Those tables would be used to join to assign the Parent_ID or Child_ID.

But this concept will fail as soon as there are duplicates within your xml file, since the assigned ID to the related InternalID won't be unique anymore.

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 4

Hi LutzM,

Those InternalID is different in all nodes.So I am trying to finding a way which process as per XML file. I mean insert one row and get the identity value,,pass that identity value to the subnode of that and again for the second row.

LutzM SSC Guru Points: 107049 More actions · Answer 5

Here's a "slightly" different approach:

Instead of Openxml I use XQuery together with CROSS APPLY for referencing outer node attributes.

To catch the ID values from the insert statements I make use of the OUTPUT INTO clause and finally join those results back to the XQuery.

DECLARE @xml XML

SELECT @xml='<Parent InternalID="154805" Action="Update">

<InsertUserID>ASDZXCCFG</InsertUserID>

<InsertDateTime>08-29-2008 17:13:57</InsertDateTime>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<UpdateDateTime>13-05-2010 13:22:41</UpdateDateTime>

<Childs>

<Child InternalID="4926250" Action="Update">

<InsertUserID>Child1</InsertUserID>

<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<UpdateDateTime>11-05-2009 15:22:44</UpdateDateTime>

<Kids>

<Kid InternalID="3406436" Action="Insert">

<InsertUserID>Kid1</InsertUserID>

<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>

<UpdateUserID>CATS</UpdateUserID>

<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>

</Kid>

<Kid InternalID="3406437" Action="Update">

<InsertUserID>KId2</InsertUserID>

<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>

<UpdateUserID>CATS</UpdateUserID>

<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>

</Kid>

</Kids>

</Child>

<Child InternalID="4926251" Action="Update">

<InsertUserID>Child2</InsertUserID>

<InsertDateTime>08-20-2008 16:27:54</InsertDateTime>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<UpdateDateTime>11-05-2009 15:22:44</UpdateDateTime>

<Kids>

<Kid InternalID="3406438" Action="None">

<InsertUserID>Kid3</InsertUserID>

<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>

<UpdateUserID>CATS</UpdateUserID>

<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>

</Kid>

</Kids>

</Child>

</Childs>

</Parent>'

DECLARE @ParentIdReference TABLE (Parent_ID INT,InternalID INT)

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('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('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('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')

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 6

krishusavalia

SSCrazy

Points: 2385

December 15, 2010 at 3:06 pm

#1263185

Hey Man ,,Thanks Alot its work fine,,,

krishusavalia SSCrazy Points: 2385 More actions · Answer 7

<InsertUserID>spowell_lic</InsertUserID>

</Father>

If I add above sunnode under the Parent node the what will be the querry for that.

I have like that senario. I have more 10 subnodes under Parent node.and some of the sunnode have subnode.

your solution will works fine in that?

LutzM SSC Guru Points: 107049 More actions · Answer 8

It sure will.

Take the code snippet for the child table.

Replace 'Childs/Child' with 'Father' in CROSS APPLY T.c.nodes('Childs/Child') U(v)

Assign a different temp table to hold the output data, if needed for further reference.

Give it a try. If you get stuck, post the related xml sample as well as what you've tried and what you expected to see as result.

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

Hey Thaanks for your fast response.

I am stuck in that.

Below is the sample XML file:

DECLARE @xml XML

SELECT @xml=

'<Parent InternalID="154805" Action="Update">

<InsertUserID>ASDZXCCFG</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<InsertUserID>Child1</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<Kids>

</Kid>

</Kid>

</Kid>

</Kids>

</Child>

<InsertUserID>Child2</InsertUserID>

<UpdateUserID>SYSTEM_USER</UpdateUserID>

<Kids>

</Kid>

</Kid>

</Kids>

</Child>

</Childs>

<InsertUserID>father1</InsertUserID>

<UpdateUserID>DULPHI_USER</UpdateUserID>

</Fathers>

<InsertUserID>father1</InsertUserID>

<UpdateUserID>DULPHI_USER</UpdateUserID>

</Mothers>

</Parent>'

Now in fathers table

father_ID int identity (1,1) primary key,

parent_ID int references Parent(Parent_ID),

InternalID int,

Action,,,,,,,,,,

So i want to pass the identity value of the Parent table to each subnode.

In you previous solution ,,we have table variable ,,which hold the value for parent_ID. but we can't use it for others subnodes.

krishusavalia SSCrazy Points: 2385 More actions · Answer 10

Can we use temp table to hold the value of Parent_ID from parent table and we can do cross apply the temp table for the subnodes to get that value??

krishusavalia SSCrazy Points: 2385 More actions · Answer 11

Finally ,,i got it,,

I used temp table for the Parents and passed its value to subnodes

krishusavalia SSCrazy Points: 2385 More actions · Answer 12

Hey LutsM,

It was great. I tried using temp tables and it works.

I was jsut working on the sample file and yesterday i got some new files, Its has two lines for the XML declaration. I tried to load the files in the table with that declaration but,,,in the message 0 rows affected for all the tables.

I know its common but i never worked with XML.

Do u have any idea how to handle it.

<?xml version="1.0" ?>

- <Mains xmlns="http://ALD.riban.com" RedegCount="0" ParentCount="2">

</Ridge>

<InsertUserID>ASDZXCCFG</InsertUserID>

krishusavalia SSCrazy Points: 2385 More actions · Answer 13

Hey LutsM,

It was great. I tried using temp tables and it works.

I was jsut working on the sample file and yesterday i got some new files, Its has two lines for the XML declaration. I tried to load the files in the table with that declaration but,,,in the message 0 rows affected for all the tables.

I know its common but i never worked with XML.

Do u have any idea how to handle it.

<?xml version="1.0" ?>

- <Mains xmlns="http://ALD.riban.com" RedegCount="0" ParentCount="2">

</Ridge>

<InsertUserID>ASDZXCCFG</InsertUserID>

LutzM SSC Guru Points: 107049 More actions · Answer 14

There are severa lissues with that new file:

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

b) There is a new node level (Mains) introduced that needs to be included in the query and finally

c) You're now dealing with xml data including a namespace declaration. Those data need to be treated differently: You'd have to add the preceeding WITH XMLNAMESPACES() clause to declare the namespace used in the document.

Here's the code to deal with data that include a namespace (I also changed the node level and I assume you can fix the issue mentioned in a) ):

;

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/Parent') T(c)

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]