Insert into Table from XML file using Stored Proc

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

Transact-SQL (2005)

Insert into Table from XML file using Stored Proc

Author

Topic

nayanancha
Starting Member

27 Posts

Posted - 2011-05-05 : 12:11:42

Hi guys..

I am new SQL programming ....I have a XML file with hundreds of tags with each of those tags as columns how can i insert them into my table

<Products>
<SKU>1</SKU>
<Desc>Book</Desc>
</Products>

Like this i have hundreds of tags and each of these tags i have as columns in my table.

I need to insert them into my table.

Please help me out

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 12:43:34

Would something like this work for you?


declare @xml xml;

set @xml = '<Products>
<SKU>1</SKU> 
<Desc>Book</Desc> 
</Products>';

insert into YourTable
select
   c.value('(./SKU)[1]','varchar(32)') as SKU,
   c.value('(./Desc)[1]','varchar(32)') as [Description]
from
   @xml.nodes ('/Products') T(c);

nayanancha
Starting Member

27 Posts

Posted - 2011-05-05 : 13:06:57

no....

It wont be static........there may be 100's of fields for each xml that I would be inserting ...so some of the tags wont be there in other xml file....

It should be dyanamic.....

INSERT INTO Products

SELECT X.product.query('SKU').value('.', 'INT'),

X.product.query('Desc').value('.', 'VARCHAR(30)')

FROM (

SELECT CAST(x AS XML)

FROM OPENROWSET(

BULK 'C:\XML\test1.xml',

SINGLE_BLOB) AS T(x)

) AS T(x)

CROSS APPLY x.nodes('Products') AS X(product);

The above one works fine but the select part should be dyanamic coz some of the xml files which have only 1 tag and others may have 5 tags....

so the way it should work is it should the read the tag in xml and insert that tag in the corresponding column of the table

Hope I am clear this time

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 13:16:49

I don't think that would be a problem. If in your query, you list EVERY column that is in the Products table, and if the XML data happens to not have some of those tags, you will simply get an empty string, 0 etc - for example:


INSERT INTO Products

SELECT 
   X.product.query('SKU').value('.', 'INT'),
   X.product.query('Desc').value('.', 'VARCHAR(30)'),
   X.product.query('Blablah').value('.','int')
...
...

This would give you a zero for the third column if the XML data did not have a Blablah element.

nayanancha
Starting Member

27 Posts

Posted - 2011-05-05 : 14:08:27

Ya thats ok....

But is there any other way becoz I need to write this

X.product.query('SKU').value('.', 'INT')

100 times for all the columns ......

Is there an alternative like * to select all other than mentioning those 100 lines for each column

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-05 : 14:27:22

SELECT 'X.Product.query(' + QUOTENAME(column_name,'''') + ').value(''.'',' + QUOTENAME(data_type, '''') + '),'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Product'

If you have varchar/nvarchar or numeric/decimal columns, you'll need to tweak that to include the length/precision/scale as needed.

nayanancha
Starting Member

27 Posts

Posted - 2011-05-05 : 15:01:44

Rob can u update your code to the above insert query....

I didn't understand how to integrate it with the above code

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-05 : 15:03:26

Run my query and paste the results into your existing query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources