Reading XML File into a table

Question

Post reply

Reading XML File into a table

nadersam

SSCertifiable

Points: 6542
More actions
July 17, 2011 at 6:34 am

#253784

Hi All,
When i try to read the attached xml file using the following commands
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, 'C:\Export1\med_Form.xml'
SELECT * FROM OPENXML(@XMLDocPointer,'/med_Form/medFormID',2 )
I get error
The XML parse error 0xc00ce556 occurred on line number 1, near the XML text "C:\Export1\Alchemy_Form.xml".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Invalid at the top level of the document.'.
Can someone help please with that, i need to read all contents of that file into a database table.
Regards
Nader

Viewing 5 posts - 1 through 4 (of 4 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

What SQL Server version do you use?

It might be easier to do it using XQuery, assuming you use SQL 2005 and up:

SELECT

v.value('medFormID[1]','int') AS ID,

v.value('FormName[1]','varchar(50)') AS FormName

FROM @xml.nodes('document') T(c)

CROSS APPLY T.c.nodes('med_Form')U(v)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nadersam SSCertifiable Points: 6542 More actions · Answer 2

Thanks very much for your reply.

I am using SQL 2008 , using your technique i must first read the xml file, should i do that using the sp_xml_preparedocument stored procedure ?.

Because it seems it's the one causing the previous error.

Thanks again

LutzM SSC Guru Points: 107049 More actions · Answer 3

You could use

SELECT * FROM OPENROWSET(

BULK 'c:\SampleFolder\SampleData3.txt',

SINGLE_BLOB) AS x

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

paul_ramster Ten Centuries Points: 1382 More actions · Answer 4

sp_xml_preparedocument doesn't read a file from disk, it creates a handle to an internal XML document (variable). To read the attached XML file into a variable, see this link[/url], or (rather more simply), follow LutzM's advice and use OPENROWSET().

DECLARE @XML xml

SELECT @xml = x.a

FROM OPENROWSET(BULK 'C:\Med_Form.xml', SINGLE_BLOB) AS x(a)

--either query with OPENXML()

DECLARE @XMLDocPointer INT

EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @xml

SELECT * FROM OPENXML(@XMLDocPointer,N'/document/med_Form/medFormID', 0)

-- or with XQuery

SELECTdoc.medFormID.value('.[1]','int') AS ID

,doc.medFormID.value('../FormName[1]','varchar(60)') AS FormName

FROM@xml.nodes('document/med_Form/medFormID') doc(medFormID)