SSIS XML and Parent Child Data Insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS XML and Parent Child Data Insert

Author

Topic

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-07 : 20:10:48

I'm new to SQL technologies and currently I'm trying to learn SSIS. I have a situation where i'v an XML file as Data Source which needs to be inserted into SQL server Destination.

My XML Hold student data in it, organized into hierarchies. The Top most contains student information Like studentid,name,section etc. The following contains other elated information regarding student Like Schedule,Fee,Attendance.

This information need to be inserted into relation tables. The problem is that I have StudenID into The Top Hierarchy and when I would insert, how would I insert studentID as foreign keyin rest of the tables.

Thanks in advance and apologies for the long post

Select Knowledge from LearningProcess

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-07 : 23:24:18

please show us the xml


<vague>
   <Student>
      <FirstName>Aussie</FirstName>
      <LastName>SQL Gury</LastName>
   </Student>
</vague>

If you don't have the passion to help people, you have no passion

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-08 : 02:25:21

Hi yosiasz

Here is the structure of the XML file.

<Students>
<Student StudentID="ST010123" BloodType="A+" Name="Geron">
<Notes>Notes about Student1</Notes>
<Campuses>
<Campus CapmusName="Capm1" Location="Loc1">
<Subjects>
<Subject Name="Physics" />
<SUbject Name="Maths" />
</Subjects>
</Campus>
</Campuses >
</Student>

<Student StudentID="ST010129" BloodType="B+" Name="Wister">
<Notes>Notes about Student2</Notes>
<Campuses>
<Campus CapmusName="Capm2" Location="Loc2">
<Subjects>
<Subject Name="English" />
<SUbject Name="Italian" />
</Subjects>
</Campus>
</Campuses >
</Student>

Select Knowledge from LearningProcess

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-08 : 12:09:27

also please provide the destination tables and their relations between each other

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-08 : 13:40:16

this is one of many approaches.


declare @xml as nvarchar(max)

declare @xmldoc as xml

set @xml = 
'<Students>
<Student StudentID="ST010123" BloodType="A+" Name="Geron">
<Notes>Notes about Student1</Notes>
<Campuses>
<Campus CapmusName="Capm1" Location="Loc1">
<Subjects>
<Subject Name="Physics" />
<SUbject Name="Maths" />
</Subjects>
</Campus>
</Campuses >
</Student>
<Student StudentID="ST010129" BloodType="B+" Name="Wister">
<Notes>Notes about Student2</Notes>
<Campuses>
<Campus CapmusName="Capm2" Location="Loc2">
<Subjects>
<Subject Name="English" />
<SUbject Name="Italian" />
</Subjects>
</Campus>
</Campuses >
</Students>'

set @xmldoc = 
'<Students>
<Student StudentID="ST010123" BloodType="A+" Name="Geron">
<Notes>Notes about Student1</Notes>
<Campuses>
<Campus CapmusName="Capm1" Location="Loc1">
<Subjects>
<Subject Name="Physics" />
<Subject Name="Maths" />
</Subjects>
</Campus>
</Campuses >
</Student>
<Student StudentID="ST010129" BloodType="B+" Name="Wister">
<Notes>Notes about Student2</Notes>
<Campuses>
<Campus CapmusName="Capm2" Location="Loc2">
<Subjects>
<Subject Name="English" />
<Subject Name="Italian" />
</Subjects>
</Campus>
</Campuses >
</Student>
</Students>'

declare @students table(StudentID nvarchar(50), Name nvarchar(50), BloodType nvarchar(50))

insert into @students
SELECT b.y.value('@StudentID', 'nvarchar(255)') StudentID   ,
       b.y.value('@Name', 'nvarchar(255)') Name    ,  
       b.y.value('@BloodType', 'nvarchar(255)') BloodType      
 FROM @xmldoc.nodes('Students') AS a(x)
CROSS APPLY x.nodes('Student') b(y)

select * from @students

SELECT b.y.value('@StudentID', 'nvarchar(255)') StudentID,
       b.y.value('Notes[1]', 'nvarchar(255)') Notes        
 FROM @xmldoc.nodes('Students') AS a(x)
CROSS APPLY x.nodes('Student') b(y)

SELECT x.value('../../@StudentID', 'nvarchar(255)') StudentID,
       x.value('@CapmusName', 'nvarchar(255)') Campus,
       x.value('@Location', 'nvarchar(255)') Location        
 FROM @xmldoc.nodes('Students/Student/Campuses/Campus') AS a(x)


SELECT x.value('../../../../@StudentID', 'nvarchar(255)') StudentID,
       x.value('@Name', 'nvarchar(255)') Name        
 FROM @xmldoc.nodes('Students/Student/Campuses/Campus/Subjects/Subject') AS a(x)

If you don't have the passion to help people, you have no passion

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-11 : 01:22:21

Hi yosiasz

This will really help me to understand XML and SQL. I just have a tag in the same file as below

<Student_Type Student_Type="Full Time" />

This is the last tag in the hirerachy and appears in the last. How would i read this tag using SQL

Thank you so much

Select Knowledge from LearningProcess

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-11 : 01:24:58

SELECT b.y.value('@StudentID', 'nvarchar(255)') StudentID,
b.y.value('Student_Type[1]', 'nvarchar(255)') Notes
FROM @xmldoc.nodes('Students') AS a(x)
CROSS APPLY x.nodes('Student') b(y)

This query outputs the student id but not the student_type.. Any idea why ?

Select Knowledge from LearningProcess

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-11 : 11:44:09

since you are LearningSQLKid let's see if you can do it on your own . try it out it is not that difficult. that way you will learn it on your own.

If you don't have the passion to help people, you have no passion

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-11 : 20:33:30

Thanks yosiasz
Let me try it .. I'll get back to you with success. i hope so :)

Select Knowledge from LearningProcess

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-11 : 21:39:12

Hi, Its me again

I Got It Working

SELECT x.value('../@StudentID', 'nvarchar(255)') StudentID,
x.value('@Student_Type', 'nvarchar(255)') Student_Type
FROM @xmldoc.nodes('Students/Student/Student_Type') AS a(x)

Friend, i still have some confusion in understanding the nodes. Would it be possible for you to send me a link to some URL wihich could help me to understand all this. As you know i'm new in this tech world i need to make sure that i got the concept right.

Cheers

Select Knowledge from LearningProcess

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-12 : 11:13:33

there will be some nausea and vertigo like symptoms when you first try to wrap you head around xml but it is worth it.

just google xml query in sql 2008.

If you don't have the passion to help people, you have no passion

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-13 : 19:56:51

Thank you so much
I appreciate you help. Can we take me one step further by giving me a hint to achieve the same via SSIS

My confusion is how would i load parent/child using SSIS. Since you had a close look at my XML file structure, how would i manage the relationsship between Campuses, Subject and Schedule.

Ok, Student Master information will go into Master Table Students the StudentID makes a relationship betwen campus and capmus_id makes relationship with schedules. I'm trying to work out on it but i would appreciate if you could please give me a second thought to work on it and take it to the next step

Thanks once again

Select Knowledge from LearningProcess

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-14 : 12:06:58

no problem, we all started somewhere and this is the place to hustle and learn, keyword here is learn

how many xml files are you receiving
How big is you xml files
how are you getting the xml file(s)? via ftp or somewhere in a file server shared location? is it emailed to you?

If you don't have the passion to help people, you have no passion

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-14 : 19:12:56

Thanks Yosiaz, for you support and encourage. I'm very excited to learn SQL and the related technologies. I need you support in my learning process. You know i've start picking it up as well which is the motivation for me.

Okay, here i go

Q:How many xml files are you receiving
A:Most likely 1 xml file every month or 20 days

Q:How big is you xml files
A:Most likely 25000 Records in file

Q:How are you getting the xml file(s)? via ftp or somewhere in a file server shared location? is it emailed to you?
A:The file will be emailed to me

Thanks

Select Knowledge from LearningProcess

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-15 : 15:54:56

is this xml file internal email or customer is emailing it to you? if it is internal have them drop it in a file location in your network.
is it being sent to your inbox or a generic Inbox. if it is your personal Inbox, do you see the problem with that? do you see a need for a better design in that?

if possible make it come to a generic Inbox or even better grab it from sender's ftp.

we are here to support you but you need to hustle and learn a lot on your own. google schmoogle and come here to learn. I take at least 15-30 minutes a day to see if I can resolve on my own almost every question posted on page one of this site. there are extremely talented people here you can learn from. dig in and research

If you don't have the passion to help people, you have no passion

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-17 : 19:33:11

Thanks.. I really appreciate your support and help. I'm googleing alot these days and learning. I like to learn on my own as it help me to remember the things with logis. Well, These days i am looking at Execute SQL task as i will be using it alot in my project.

I'm also trying to download books on SSIS. Can you rekon any good book considering my current scenario.

I'm loving Intergration Services... Its simply amazing.

Select Knowledge from LearningProcess

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-18 : 12:08:08

yes SSIS is a beauty but it does have some limitations on some things. I deal with a lot of xml files some 18GB SSIS was not the solution in this scenario. So I had to use SQLXMLBulkload which gobbled up that 18gb of xml file within minutes.


set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")

SQL Task is the simplest of them, there are other ssis objects that can do some amazing stuff

there are many books out there but it depends on your need. Go to the closest book store, grab some coffee and start browsing through them.

If you don't have the passion to help people, you have no passion

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-19 : 02:13:15

Yup right, i agree. SSIS is the beauty and wwhat i like is that most of the task it can manage using the memory without stressing the database again and again

These days i am working to find out that how can i load data from xml source into diffrent tables having one to many relationship. I will have to consider if parent child exists then i would have to update the record and same goes for the child record as well..

so these days i am reading diffrent topics and diffrent approaches on this.

Select Knowledge from LearningProcess

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-19 : 02:31:23

--Hey try this first create this function in your user database and at end you run this function using your data in select statement

Use [your database]---Here your database name
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[XMLTable](
@x XML
)
RETURNS TABLE
AS RETURN
/*----------------------------------------------------------------------
This INLINE TVF uses a recursive CTE that processes each element and
attribute of the XML document passed in.
----------------------------------------------------------------------*/
WITH cte AS (
/*------------------------------------------------------------------
Anchor part of the recursive query. Retrieves the root element
of the XML document
------------------------------------------------------------------*/
SELECT
1 AS lvl,
x.value('local-name(.)','NVARCHAR(MAX)') AS Name,
CAST(NULL AS NVARCHAR(MAX)) AS ParentName,
CAST(1 AS INT) AS ParentPosition,
CAST(N'Element' AS NVARCHAR(20)) AS NodeType,
x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,
x.value('local-name(.)','NVARCHAR(MAX)')
+ N'['
+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)
+ N']' AS XPath,
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position,
x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,
x.value('text()[1]','NVARCHAR(MAX)') AS Value,
x.query('.') AS this,
x.query('*') AS t,
CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,
CAST(1 AS INT) AS ID
FROM @x.nodes('/*') a(x)
UNION ALL
/*------------------------------------------------------------------
Start recursion. Retrieve each child element of the parent node
------------------------------------------------------------------*/
SELECT
p.lvl + 1 AS lvl,
c.value('local-name(.)','NVARCHAR(MAX)') AS Name,
CAST(p.Name AS NVARCHAR(MAX)) AS ParentName,
CAST(p.position AS INT) AS ParentPosition,
CAST(N'Element' AS NVARCHAR(20)) AS NodeType,
CAST(
p.FullPath
+ N'/'
+ c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)
) AS FullPath,
CAST(
p.XPath
+ N'/'
+ c.value('local-name(.)','NVARCHAR(MAX)')
+ N'['
+ CAST(ROW_NUMBER() OVER(
PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
ORDER BY (SELECT 1)) AS NVARCHAR )
+ N']' AS NVARCHAR(MAX)
) AS XPath,
ROW_NUMBER() OVER(
PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
ORDER BY (SELECT 1)) AS Position,
CAST(
SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1)
+ c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)
) AS Tree,
CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value,
c.query('.') AS this,
c.query('*') AS t,
CAST(
p.Sort
+ CAST( (lvl + 1) * 1024
+ (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)
) AS VARBINARY(MAX) ) AS Sort,
CAST(
(lvl + 1) * 1024
+ (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT
)
FROM cte p
CROSS APPLY p.t.nodes('*') b(c)
), cte2 AS (
SELECT
lvl AS Depth,
Name AS NodeName,
ParentName,
ParentPosition,
NodeType,
FullPath,
XPath,
Position,
Tree AS TreeView,
Value,
this AS XMLData,
Sort, ID
FROM cte
UNION ALL
/*------------------------------------------------------------------
Attributes do not need recursive calls. So add the attributes
to the query output at the end.
------------------------------------------------------------------*/
SELECT
p.lvl,
x.value('local-name(.)','NVARCHAR(MAX)'),
p.Name,
p.Position,
CAST(N'Attribute' AS NVARCHAR(20)),
p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),
p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),
1,
SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)
+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),
x.value('.','NVARCHAR(MAX)'),
NULL,
p.Sort,
p.ID + 1
FROM cte p
CROSS APPLY this.nodes('/*/@*') a(x)
)
SELECT
ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,
ParentName, ParentPosition,Depth, NodeName, Position,
NodeType, FullPath, XPath, TreeView, Value, XmlData
FROM cte2

----------------And at last this select statement yosing your data

select * FROM dbo.XMLTable('
<Student StudentID="ST010123" BloodType="A+" Name="Geron">
<Notes>Notes about Student1</Notes>
<Campuses>
<Campus CapmusName="Capm1" Location="Loc1">
<Subjects>
<Subject Name="Physics" />
<SUbject Name="Maths" />
</Subjects>
</Campus>
</Campuses >
</Student>
<Student StudentID="ST010129" BloodType="B+" Name="Wister">
<Notes>Notes about Student2</Notes>
<Campuses>
<Campus CapmusName="Capm2" Location="Loc2">
<Subjects>
<Subject Name="English" />
<SUbject Name="Italian" />
</Subjects>
</Campus>
</Campuses >
</Student>') where [Value] is not null order by XPath

--- Once this select stament run correct then see the Value column. I think this is what you need...

Raghu' S

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-20 : 18:56:53

Thanks raghuveer125
I was very helpful

Select Knowledge from LearningProcess

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-04-21 : 03:23:09

Ok, Let me get your professional advise
Consider the above XML file.

Task: Trying to import XML file <Staudent> above
Appraoch:

Step 1 : One XML source that pionts to the XML file
Step 2 : Each node that is exposed by xml source is linked to OLE DB Destination. If there are three nodes then there will be three OLE DB Data Destinations. One for each node

Question: Am i doing it right ? Is my Approach Correct ?

Select Knowledge from LearningProcess

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources