Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reading XML File into a table Expand / Collapse
Author
Message
Posted Sunday, July 17, 2011 6:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:18 AM
Points: 85, Visits: 205
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


  Post Attachments 
Med_Form.txt (31 views, 31.36 KB)
Post #1143075
Posted Sunday, July 17, 2011 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 7,042, Visits: 12,971
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1143076
Posted Sunday, July 17, 2011 6:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:18 AM
Points: 85, Visits: 205
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
Post #1143077
Posted Sunday, July 17, 2011 7:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 7,042, Visits: 12,971
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1143080
Posted Tuesday, September 13, 2011 7:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 17, 2014 6:38 AM
Points: 278, Visits: 534
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, 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
SELECT doc.medFormID.value('.[1]','int') AS ID
, doc.medFormID.value('../FormName[1]','varchar(60)') AS FormName
FROM @xml.nodes('document/med_Form/medFormID') doc(medFormID)

Post #1174112
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse