What T-SQL script to export an XML file content in an SQL table ?

Hi,

I'm on a datawarehouse (DW)  project actually and i need your help !
I got an historic of my transactions in an xml format.
I'd like to export these transactions in the tables of my DW.

First there is a mismatch between my xml structure and the structure of my transaction table.
You'll find the details in the attachements.

I found an example of script on the web : http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx 

This script works but i don't know how to adapt it to my transaction table.
I want to take the details of the transactions from the xml files and put them in the transaction table.

Can you help me ?

Thanks.
xml.txt
transactions.txt
friscobayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pssandhuCommented:
I haven't looked at your attachments as yet, but why don't you do a bulk insert into a temp table and then write a sql to get data, modify it if you want then insert it into the transaction table. I think it will easier this way.
P.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
friscobayAuthor Commented:
But what is the procedure for a BULK Insert please ? i'm stuck ...
0
friscobayAuthor Commented:
Do you have sample code that can help
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pssandhuCommented:
Here is Microsoft Article on xml reading: http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx
Another article: http://www.sql-server-performance.com/articles/dev/xml_data_2005_p1.aspx
Reading XML Data: http://www.builderau.com.au/program/sqlserver/soa/Reading-an-XML-file-from-SQL-Server/0,339028455,339174685,00.htm
All these will give you an idea of how to read XML data. As I suggested before, try and importing the data as it is into a staging table and then use the stagnig table to get the data into your transactions table.
Hope that helps.
P.
0
friscobayAuthor Commented:
Hi,

You last post really helped me.
I used the Microsoft article and i found another article linked to it:
http://msdn.microsoft.com/en-us/library/aa225723(SQL.80).aspx

i followed the procedure  and i got an error (attachements).
You can Check the vbscript too.
Can you help me to solve the runtime error with the DTS?

Thanks.

vbscript-BLoad.txt
dts-script-task-runtime-error.png
0
friscobayAuthor Commented:
the solution is incomplete ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.