Solved

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

Posted on 2009-07-06
6
617 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Import New Records From Access Table To SQL Database Table 7 30
SQL Pivot with row total 5 26
tempdb log keep growing 7 33
Run Stored Procedure uisng ADO 5 20
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 learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

839 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