how to load and prepare existing XML doc with sp_xml_preparedocument

Question

how to load and prepare existing XML doc with sp_xml_preparedocument

Steven M

Mr or Mrs. 500

Points: 548
More actions
September 5, 2007 at 9:42 am

#176546

i'm need to import xml to my database. i've looked around and found a couple of articles on doing this via sp_xml_preparedocument and openXML. however, the samples i've found for using sp_xml_preparedocument assume the xml to be loaded is a varchar(X) variable:
DECLARE @hdoc int
DECLARE @doc varchar(8000)
SET @doc ='<ROOT>
<Customers/>
</ROOT>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
what i want to do is load up an existing XML document:
DECLARE @hdoc int
DECLARE @doc varchar(8000)
SET @doc ='c:\myData.XML'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
Running this returns "Invalid at the top level of the document". error message.
any ideas how to load an existing XML doc?
tks

Viewing 6 posts - 1 through 6 (of 6 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Stolen directly from BOL:

INSERT INTO docs SELECT 10, xColFROM    (SELECT * FROM OPENROWSET       (BULK 'C:\temp\xmlfile.xml',      SINGLE_BLOB) AS xCol) AS R(xCol)

Peruse this articles and its links - lots of goodies:

http://msdn2.microsoft.com/en-us/library/ms345117.aspx

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steven M Mr or Mrs. 500 Points: 548 More actions · Answer 2

thanks matt, but i don't think this will work. i need to read specific nodes in the xml structure and insert to particular columns in my tables. the method you mention will simply copy the entire xml structure to a table column.

any other ideas?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

well - keep in mind that you can use an XQUERY syntax within the OPENROWSET statement to pick out specific nodes. look into the xml.nodes notation in BOL (second article included) - should get you pretty far. There's a wonderful reference called XML Best Practices in SQL 2005 that is usually my go-to document when I get stuck with these (first article). Some reading:

http://msdn2.microsoft.com/en-us/library/ms345115.aspx

http://msdn2.microsoft.com/en-us/library/ms188282.aspx

Give me some more specifics (XML schema of the input file, and what kinds of records you want to pull in), and I will see what we can do to help.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steven M Mr or Mrs. 500 Points: 548 More actions · Answer 4

thanks matt.

i was just reading up on the xml.nodes and xml.value methods and think this should work fine. i've revised my import script so i first load the xml file into an xml column and then parse like so:

-- first delete all data from xml data --

delete from xmlData

delete from tblAccounts

-- load the xml data file into sql --

INSERT INTO xmlData SELECT xCol FROM

(SELECT * FROM OPENROWSET (BULK 'c:\account_details.xml', SINGLE_BLOB) AS xCol) AS R(xCol)

DECLARE @sXmlDoc XML

select @sXmlDoc = xmlDoc from xmlData

-- parse xml for the data we need --

insert into tblAccounts

SELECT

C.value('(AccountInformation/DistributorAccountNumber)[1]','varChar(100)') as distributorAccountNumber,

C.value('(ConsumerInformation/LastName)[1]','varChar(100)') as lastName

FROM @sXmlDoc.nodes('/ChangeConsumerInfoRequest') T(C)

Go

tks again.

noeld SSC Guru Points: 96590 More actions · Answer 5

have a look at OPENXML ... BUT you must use a TEXT variable not a varchar(8000)

* Noel