Load the data from XML file to Sql Server 2005 database

Question

Load the data from XML file to Sql Server 2005 database

vkoka

SSC Enthusiast

Points: 190
More actions
December 1, 2008 at 12:05 am

#124702

hi
i hava a problem to load the data from XML file to Sql Server 2005 data base. XML data is present at this level given here
<XXX(TOP Most PArent)
<YYY>
<CCC>
<ZZZ>
<BBB>
<TAG>
<TAG1 at1="ABCD" value="efgh"\>
<TAG2 at1="PQRS" value="wxyz"\>
<\TAG>
<\BBB>
<\ZZZ>
<\CCC>
<\YYY>
<\XXX>
here i want to get the data from tag Node.
i want the data in a table like this as at1 as column name and the at1 value as data.
--Column Names ABCD |PQRS
--Data efgh |WXYZ
can any one help me on this
thanks in advance...

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Maybe there is a better way but I think that you’ll have to use dynamic SQL with Xquery. The Xquery part will get the values of the attributes and then you’ll use the dynamic SQL to with those values. Here is an example:

declare @xml xml

declare @sql varchar(300)

set @xml =

'

select @sql = 'insert into MyTable (' + @xml.value('(XXX/YYY/CCC/ZZZ/BBB/TAG/TAG1/@at1)[1]','varchar(30)') +

', ' + @xml.value('(XXX/YYY/CCC/ZZZ/BBB/TAG/TAG2/@at1)[1]','varchar(30)') + ') values ('''

+ @xml.value('(XXX/YYY/CCC/ZZZ/BBB/TAG/TAG1/@val)[1]','varchar(30)') + ''','''

++ @xml.value('(XXX/YYY/CCC/ZZZ/BBB/TAG/TAG2/@val)[1]','varchar(30)') + ''')'

select @sql

By the way notice that I modified your XML example to make it valid XML. In your original xml you used the character \ instead of / to close tags. You also used value as the attribute name, so I modified it to val.

Adi

P.S

For some reason, I can't see the XML itself. I used the same XML as you did with few modifications. The modifications that I already mentioned (modifying closing character in closing tags and modifying attribute name "value" to "val"). Beside that I fixed the root tag and moved the remark from it. If anyone can let me know why we can't see the XML that I used in my script, I'll be happy to post it as well. In any case I attached the script with the XML in the attached file.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vkoka SSC Enthusiast Points: 190 More actions · Answer 2

hi Adi

thanks for your reply with a solution. i am starter in SQl Server so can u please help me.

i have the following needs

1) i get thousands of xml every day, so i need to pass these xmls from a folder

2) i have to put this xmls content in to the @xml dynamically.

please can you suggest a solution for this--

by the way you have to replace ' ' with > with in the XML.

Ex :

for '<' use ampersandLT;

'>' use AmpersandGT;

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

You can do it with the openrowset function and the provider bulk.

Here is an example:

create table #XmlTable (XmlCol xml)

go

insert into #XmlTable (xmlCol)

select BulkColumn

from openrowset(bulk 'c:\FileName.xml', single_blob) as dt

You can also run also assign the XML to a vairbale:

declare @xml xml

select @xml = convert(xml,BulkColumn)

from openrowset(bulk 'c:\Customers.xml', single_blob) as dt

There is also the SQLXMLBulkLoad utility that can import xml files very fast and insert each part of the XML into the correct table and column according to a supplied mapping. The problem is that in your case, you get the mapping from the XML and you don’t know it before you start the import. Maybe there is a way to import the data and decide how to import it during run time, but I don’t know about it. You can consider how ever loading all the xml files into a staging table and then start working with the XML. In any case you can find lots of information about openrowset with bulk provider or SQLXMLBulkLoad utility on the net. Let me know if you need more help.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vkoka SSC Enthusiast Points: 190 More actions · Answer 4

hi Adi

thanks for the quick reponse i will try and let u know and if any help i will ping u .

thanks

krishusavalia SSCrazy Points: 2385 More actions · Answer 5

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

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

---Above is the Expected result for the Kid Table

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