XML Parse help

Question

XML Parse help

SGT_squeequal

SSCertifiable

Points: 7167
More actions
January 4, 2012 at 8:46 am

#249129

Hello, i am trying parse an xml document into SQL server 2008r2 but am having trouble, can anyone help?
This is my task
I have a xml doc in a location, c:\somefolder\somefile.xml
i want to open this in SQL so i can query the information to list out some of the fields
So far i have tried using
exec sp_xml_preparedocument @idoc output, @xml_in
however apparently you cant use this to parse a file so i tried
SELECT * FROM OPENROWSET(BULK N'C:\somefolder\somefile.xml', SINGLE_BLOB) AS Document;
but this creates what looks like hex
can anyone help
***The first step is always the hardest *******

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

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

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 1

There might be a better way to do this but I have always put the xml files in a table (I usually need them stored in SQL anyways) and then do whatever parsing afterwards. Something like below.

create table xml_table(xml_data xml)

insert into xml_table(xml_data)

select * from openrowset(bulk 'C:\xmlfile.xml', single_blob) as xmldata

--Do your xml parsing here

You may be able to skip this step but it will give you an xml column to query from.

GSquared SSC Guru Points: 260824 More actions · Answer 2

Change SINGLE_BLOB to SINGLE_CLOB. BLOB is for "Binary Large OBject", CLOB is for "Character Large OBject". Using the BLOB version makes SQL Server assume it's binary data, which will, indeed, look like hex.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 3

Ahh, I didn't even think of that. Overlooked the single_blob in that query. QSquared is right, change it to single_clob and you're good.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 4

thank you all very much

***The first step is always the hardest *******

Brandon-1000181 Old Hand Points: 318 More actions · Answer 5

The problem isn't the format of the data (clob vs blob), you just need to specify to SQL that you are working with XML. Try:

DECLARE @xml XML

SELECT @xml = BulkColumn

FROM OPENROWSET(BULK 'C:\Temp\SampleData.xml', SINGLE_BLOB) AS XMLData

SELECT @xml