How to query XML files from sql server

Zaph0d

I have to query some data produced by an application (no source code) that stores half its data in a sql server databse and plonks the rest into an xml file.

There is no xml schema file for the xml. I have to guess it from sample files.

There is a sql xml bulk load utility that can upload an xml file into sql server but there seems to be an 8000 character limit on the size of a variable I can run an Xpath query against. The xml file is over 100,000 chars.

How am I going to do this?

I fucking hate xml.

akari no ryu

Zaph0d wrote:

There is a sql xml bulk load utility that can upload an xml file into sql server but there seems to be an 8000 character limit on the size of a variable I can run an Xpath query against. The xml file is over 100,000 chars.

How am I going to do this?

This may be easier if we could get the schema for the table and a sample from the xml
If you're using something like PERL, PHP, JAVA or any other half decent language, you'll have the ability to access the xml as a dom tree. That will probably make your life easier.

theexis

You should be able to do this if you shred the XML in SQL, uploading it via a TEXT / NTEXT sproc variable. Take a look at sp_xml_preparedocument and OPENXML.

mewso

The way I would do this is simply download VB 2005 express, open the xml file into a dataset (has the ability to infer the schema) and assign to a datagrid on windows form. Now I can see the structure of the xml file and write my code to loop through each datarow in the dataset and do what I wish with it.