Solved

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

Posted on 2009-07-06
6
620 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:friscobay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24787582
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
 

Author Comment

by:friscobay
ID: 24795181
But what is the procedure for a BULK Insert please ? i'm stuck ...
0
 

Author Comment

by:friscobay
ID: 24850941
Do you have sample code that can help
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 500 total points
ID: 24851194
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
 

Author Comment

by:friscobay
ID: 24864170
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
 

Author Closing Comment

by:friscobay
ID: 31600276
the solution is incomplete ...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question