Loading XML file with Multiple element

Question

Post reply

Loading XML file with Multiple element

krishusavalia

SSCrazy

Points: 2385
More actions
December 7, 2010 at 8:11 pm

#230171

Hi,
I am new in SSIS and XML.I am trying to load an XML file into SQL server tables using SSIS. Below is the the code for my XML file.
1) In data flow i am using XML source and OLEDB destination.
2) when I am draging the arrow from XML source to OLEDB destination,,its showing me 6 tables. but from that I need just 2 tables. Agent and Address.
3) Now when I execute the package,,In the Agent table its giving meAgent_Id column and its value is 1.
i am not getting it that how that column comes from becz in the XML file i am not seeing it.
4) I want to create relationship between the two tables(Agent and Address).
5) I need Agent_Id column as an identity column and I need the same Identity column(Agent_Id) in the Address table.
Is the any way that I can achive it using SSIS?
Below is my XML file.

Viewing 3 posts - 1 through 2 (of 2 total)

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

arun_kumar14 SSC Enthusiast Points: 109 More actions · Answer 1

arun_kumar14

SSC Enthusiast

Points: 109

December 13, 2010 at 7:04 am

#1261683

file is missing. can you send xml file.

krishusavalia SSCrazy Points: 2385 More actions · Answer 2

--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>

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

-----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 thisChild_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