XML file import into a SQL table

Question

Post reply

XML file import into a SQL table

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
March 16, 2004 at 10:23 pm

#160653

Hi! Does anybody know what the most efficient way of copying an XML file format into a SQL table? Your wisdom is much appreciated...
- Dave

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Peter Evans Ten Centuries Points: 1187 More actions · Answer 1

Check out OPENXML. http://msdn.microsoft.com/library/en-us/xmlsql/ac_openxml_1cx8.asp

Allot depends on how you want to map the elements of your XML to relational data base tables.

Peter Evanns (__PETER Peter_)

Joel_sjoo SSC Veteran Points: 282 More actions · Answer 2

I have used the xmlbulkload function with a VB script to import xml to SQL. I think I saw a KB article at Microsoft about it. Look there.

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 3

Thanks! I've actually been researching this. I'm assuming I need VB 6 to run this script, right?

Guy Staat SSC Journeyman Points: 85 More actions · Answer 4

You don't need VB6 to run VBS scripts. Windows has the ability to run VBS scripts without needing anything else.

Guy

Joel_sjoo SSC Veteran Points: 282 More actions · Answer 5

As Guy say, you don't need VB on the computer. Just make an *.vbs file and windows execute it. Exampel:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

objBL.ConnectionString = "provider=SQLOLEDB.1;data source=Servername;database=test;Integrated Security=sspi"

objBL.ErrorLogFile = "e:\temp\error.log"

objBL.Execute "e:\temp\schema.xml", "e:\temp\test.xml"

Set objBL = Nothing

The schema file must map your collumns i the table.

Best luck! /Joel

sholliday SSCrazy Points: 2100 More actions · Answer 6

Here is a sample, using the input as a text object.

--first , create the stored procedure.

if exists (select * from sysobjects

where id = object_id('dbo.usp_insert_pubs_authors') and sysstat & 0xf = 4)

drop procedure dbo.usp_insert_pubs_authors

GO

CREATE PROCEDURE usp_insert_pubs_authors (

@xml_doc TEXT )

AS

SET NOCOUNT ON

DECLARE @hdoc INT -- handle to XML doc

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc

-- build a table (variable table) to store the xml-based result set

DECLARE @storesinsert TABLE (

stor_id varchar(16) ,

stor_name varchar(16) ,

stor_address varchar(16) ,

city varchar(16) ,

state varchar(16) ,

zip varchar(16)

)

INSERT @storesinsert

SELECT

stor_id ,

stor_name ,

stor_address ,

city ,

state ,

zip

FROM

-- use the correct XPath .. the second arg ("2" here) distinquishes

-- between textnode or an attribute, most times with

--.NET typed datasets, its a "2"

OPENXML (@hdoc, '/StoresDS/store', 2) WITH (

stor_id varchar(16) ,

stor_name varchar(16) ,

stor_address varchar(16) ,

city varchar(16) ,

state varchar(16) ,

zip varchar(16)

)

-- temp select just to show all the data in the @variabletable

select * from @storesinsert

BEGIN TRANSACTION

--insert into the stores table ( a "real" table) .. (from) the @variabletable (which now contains all the dataset data)

Insert into stores

(

stor_id ,

stor_name ,

stor_address ,

city ,

state ,

zip

)

SELECT

stor_id ,

stor_name ,

stor_address ,

city ,

state ,

zip

FROM @storesinsert

IF @@ERROR <> 0

BEGIN

RAISERROR('error updating database', 16, 1)

ROLLBACK Transaction

END

COMMIT TRAN

GO

--

--Now run the code using an example

--

--Here is the call. The input for the t-sql proc is

--text, but tsql doesn't allow localized text parameters

--so varchar is used here (for @xml_doc_temp).

--The xml string (below) follows what would be a typed dataset xml

--While here, it is manually created, the procedure would

--probably be called using the myTypeDataset.getXML() as the input parameter

--Naturally,the xml can contain 0, 1 or N number of "stores" for

--insertion (2 stores are used here)

declare @xml_doc_temp varchar(8000)

select @xml_doc_temp =

'

<store>

<stor_id>' + LEFT(NEWID(), 4) + '</stor_id>

<stor_name>' + LEFT(NEWID(), 16) + '</stor_name>

<stor_address>456 Hickory</stor_address>

<city>Charlotte</city>

</store>

<store>

<stor_id>' + LEFT(NEWID(), 4) + '</stor_id>

<stor_name>' + LEFT(NEWID(), 16) + '</stor_name>

<stor_address>456 Main</stor_address>

<city>Charlotte</city>

</store>

</StoresDS>

'

print @xml_doc_temp

EXEC usp_insert_pubs_authors @xml_doc_temp

GO

select * from stores order by stor_name

Maybe that will get you started.

Paul Young-219568 Grasshopper Points: 21 More actions · Answer 7

The schema file, is this a style sheet, XSD, XSL, etc?

How do you map the columns? Have you got an example of what syntax is stored in there?

Joel_sjoo SSC Veteran Points: 282 More actions · Answer 8

Something like this i think. Tablename is test with columns name, title and date:

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

</ElementType>

</ElementType>

</Schema>