How to import XML documents to SQL server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

How to import XML documents to SQL server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-25 : 08:07:49

Chris writes "Does anyone know how I can import XML documents to SQL server. I have an online database that exports the data in an XML format example below and I want to be able to import this to my main databse on SQL server. Thanks for your help.


<?xml version="1.0" encoding="iso-8859-1" ?> 
- <DATASET>
  <TYPE>success</TYPE> 
- <RECORD>
  <DATA type="type" id="open">575131</DATA> 
  <DATA type="subject">The revised Association</DATA> 
  <DATA type="delivery">23640</DATA> 
  <DATA type="timestamp">1114119467</DATA> 
  <DATA type="date">2005-04-21</DATA> 
  <DATA type="frequency">2</DATA> 
  </RECORD>
- <RECORD>
  <DATA type="type" id="open">575134</DATA> 
  <DATA type="subject">PROOF: The revised Association</DATA> 
  <DATA type="delivery">20220</DATA> 
  <DATA type="timestamp">1114115945</DATA> 
  <DATA type="date">2005-04-21</DATA> 
  <DATA type="frequency">3</DATA> 
  </RECORD>
</DATASET>

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-27 : 21:27:35

I've looked into this as well and unfortunately its not too straight forward.

Here is a good link

http://www.microsoft.com/sql/techinfo/tips/development/importingxmlfile.asp

Mike Petanovitch

bluemetal
Starting Member

26 Posts

Posted - 2005-04-28 : 02:03:30

Hi, The Best Method that i know is using OPENXML. Just create a Stored Proc like the following, be sure to create a table first with all the needed columns. Then form your Openxml SELECT according to the XML structure. I've tried to show you below how to access the Node's Element and Node's Data. Keep continuing like this till you have all the XML structure catered to, hope this helps.

Call the proc like this (pass the whole xml text as argument)

Exec sp_InsertXMLtoSQL '<xml></xml>'

CREATE PROCEDURE sp_InsertXMLtoSQL

@strXML ntext

AS
DECLARE @iDoc int
EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML --system proc, no need to worry where it is.

INSERT INTO tbl_MYTABLE(type, id, data) --make your own table and insert according to columns

(SELECT * FROM OpenXML(@iDoc, '/DATASET/RECORD', 2) --specify the node depth
WITH
(TYPE VARCHAR(10) '@TYPE', -- pick up the attribute, case sensitive
ID CHAR(4) '@ID' -- pick up other attributes (datatypes should match
DATA VARCHAR(10) -- pick up the node's data .. and so on
)

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 12:32:03

Bluemetal,
Do you know of an easy way to get a xml doc streamed into a sql parameter??
Using the tool sql server provides you with?

Mike Petanovitch

mfemenel
Professor Frink

1421 Posts

Posted - 2005-04-28 : 13:13:18

We use a stored procedure that takes 3 text variables. Our app passes xml into those text variables and then i pull it into 3 table variables to do processing. if it's simple data that doesn't need a lot of manipulation(mine does) you can just go straight from the openxml call to your tables. Performance is good on this. We do a few million transactions a week and average about 1 second. Here's a quick sample:
CREATE procedure api_setDisposition
@offerdoc text,@subdoc text,@mapdoc text
as

declare @offers table (
offer_id int,
campaign_id bigint,
program_id bigint,
offer_level varchar(40),
ban decimal(9,0),
channel varchar(40),
interaction_id varchar(255),
userid varchar(20),
disposition_id int,
manual_targeting bit,
oid varchar(20),
category_id int,
customer_id bigint,
iteration_code smallint )

exec sp_xml_preparedocument @idoc Output,@offerdoc
insert into @offers(offer_id,campaign_id,program_id,offer_level,ban,channel,iteration_code,interaction_id,userid,disposition_id,manual_targeting,oid)
select * from openxml(@idoc,'/offers/offer',3)
with(
offer_id int,
campaign_id bigint,
program_id bigint,
offer_level varchar(40),
ban decimal(9,0),
channel varchar(40),
iteration_code_id tinyint,
interaction_id varchar(255),
userid varchar(20),
disposition_code int,
manual_targeting bit,
oid varchar(20) '@oid')

Mike
"oh, that monkey is going to pay"

bluemetal
Starting Member

26 Posts

Posted - 2005-04-28 : 22:05:12

Hey mpetanovitch,
I was looking for the EASY way too, a tool or something that the DTS would provide, but unfortunately theres not. I have written a VB script code and use that in the "active-x" script in the DTS to call the Stored Proc, maybe it'll work for you.

The only problem is, OpenXML natively uses "bulkinsert" within, which sometimes crashes while importing larger files .. which is vey crappy! I still dont know what to do.

This is the code, it will parse all the XML documents in a folder and pass on to the SP. I have also passes the filename to the SP and stored it with the record to provide additional info. REMEMBER that XML Parsing is CASE SENSITIVE .. even the file extension if its in caps or not.

=====
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit

Function Main()

Dim objXMLDOM
Set objXMLDOM = CreateObject("MSXML2.DOMDocument")

Dim objADOCnn
Set objADOCnn = CreateObject("ADODB.Connection")
objADOCnn.Open "PROVIDER=SQLOLEDB.1;SERVER=YOURSERVER;UID=YOURID;PWD=YOURPWD;DATABASE=YOURDB;"
objADOCnn.CommandTimeout = 0

Dim strSQL, strXML, strFile, strPathFile, sFileName
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFile

sFolder = "C:\Projects\XMLfiles\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)

For Each fsoFile in fsoFolder.Files
strFile = fsoFile.Name
if FSO.GetExtensionName(fsoFile.Name) = "XML" then
sFileName = sFolder & fsoFile.Name
end if

strPathFile = sFolder & fsoFile.Name
If NOT objXMLDOM.Load(sFileName) Then
MsgBox "Error: " & objXMLDOM.parseError.reason
Exit Function
End If

strSQL = "exec sp_InsertXMLData '" & objXMLDOM.xml & "', '" & strFile & "'"
objADOCnn.Execute strSQL
Next

objADOCnn.Close
Set objADOCnn = Nothing
Set objXMLDOM = Nothing

Main = DTSTaskExecResult_Success

End Function

=====

crudmop
Starting Member

14 Posts

Posted - 2005-11-17 : 23:21:33

Bluemetal.
I am willing to have your children, if you'd like.

I have been trying to come up with a simple, dirty method of feeding a group of xml files from a directory in via OPENxml - and have been banging my head against the wall trying to come up with a reasonable method to feed largescale XML docs in succession. Your script has allowed me to once again go to sleep without struggling over possible solutions.

I know this post was months ago, but see how your solutions are so far reaching?

Honestly, thanks for posting this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources