Solved

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

Posted on 2009-07-06
6
613 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now