Import XML data - SQL Server Forums

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 2005 Forums

SSIS and Import/Export (2005)

Import XML data

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-05 : 17:59:36

I'm trying to import some XML files into SQL 2005 (see sample below). Is there a fast way to do this with a wizard? Or any simple method?


<SQLReconResults>
  <SQLReconResult>
    <ServerIP>145.137.125.136</ServerIP>
    <TCPPort>1433</TCPPort>
    <ServerName>MYSERVER.MYDOMAIN.COM</ServerName>
    <InstanceName>MSSQLSERVER</InstanceName>
    <BaseVersion />
    <SSNetlibVersion>9.0.3054</SSNetlibVersion>
    <TrueVersion />
    <ServiceAccount>MYDOMAIN\MYSERVICEACCOUNT1</ServiceAccount>
    <IsClustered />
    <Details>(WMI)StartMode:Auto State:Running Path:"C:\My_Path\sqlservr.exe"</Details>
    <DetectionMethod>TCP WMI SCM</DetectionMethod>
  </SQLReconResult>
</SQLReconResults>

CODO ERGO SUM

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-05 : 18:47:09

Does this help: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/689297f3-adb0-4d8d-bf62-cfda26210164.htm
Proc: sp_xml_preparedocument

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-05 : 19:32:39

[code]
DECLARE @doc int
EXECUTE sp_xml_preparedocument @doc OUTPUT, @xml -- @xml is nvarchar containing your xml

INSERT dbo.mytable(col1, ... )
SELECT ServerIP, ... FROM OpenXML(@doc, '/SQLReconResults/SQLReconResult', 2)
WITH
(
ServerIP varchar(20), ...
)

EXECUTE sp_xml_removedocument @doc
[/code]

elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-05 : 20:11:14

I want to be able to import FILES containing XML.

OPENXML looks like it works with data that is contained in a variable, but how do I import data directly from a file?

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-05 : 21:29:42

in that case you might check out the SQLXMLBulkLoad class and friends: http://technet.microsoft.com/en-us/library/ms171993.aspx

here's an example: http://technet.microsoft.com/en-us/library/ms171806.aspx

elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-06 : 09:34:37

I was hoping for a Wizard.

I find it a little amazing that I can insert this data into Excel with a few click strokes, but SSIS won't do it in the Import Wizard.

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-06 : 09:58:53

That surprises me too - I've never used SSIS or DTS for anything but I would have thought SSIS would have a task for importing xml. they really don't? This article says they do: "One of the exciting new data sources supported by SSIS is an XML document."
http://www.databasejournal.com/features/mssql/article.php/3580216

also this post says you can do it with SSIS and a "dataset mapping" whatever that is:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=744384&SiteID=1

finally, you could get the cds of everyone's favorite sql trainer (you know who i mean). On cd 15, topic 13, there is this topic: "Importing XML Content with SSIS".

elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-06-06 : 13:52:22

Apart from the hassle of making a Schema, SQLXMLBulkLoad is seriously fast. Worth the effort for recurring tasks. I've got a 10 line VBScript file here [which we use as a framework for imports] if it would give you a leg-up

Mind you, there are some nested relationships I've encountered that I've never managed to define a Schema for ... but I was always able to work around with sp_xml_preparedocument

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-06 : 14:39:19

quote:
Originally posted by Kristen

Apart from the hassle of making a Schema, SQLXMLBulkLoad is seriously fast. Worth the effort for recurring tasks. I've got a 10 line VBScript file here [which we use as a framework for imports] if it would give you a leg-up

Mind you, there are some nested relationships I've encountered that I've never managed to define a Schema for ... but I was always able to work around with sp_xml_preparedocument

Kristen

Could you post your script?

I have been working throught the procedure in this link:
http://www.databasejournal.com/features/mssql/article.php/3675356

I was able to load a directory of XML files into a table, and should be able to cursor through it with sp_xml_preparedocument, etc.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-06-07 : 01:44:17

Send me a private message MVJ and I'll attach my script. (Its not in shape for public consumption)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources