Solved

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

Posted on 2009-07-06
6
615 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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