Reading an XML file from SQL Server

You guys totally rock!!!

This was the solution that I have hunted for some time... brilliant; only thing is every XML file that I am working with is greater than 8000 characters long. I can easily enough spread the file across several varchar(8000) variables; however, I can't find a reasonable means to concatinate them for use with the sp_xml_preparedocument command... any suggestions?

» Report offensive content

what if the file size is greater than what can be stored in @filecontents (i.e) greater than 8000 which can be stored using varchar?

» Report offensive content

The BOL isn't missing anything. The addition of reading in a text file isn't part of the OpenXML problem. Also, using xp_cmdshell for this is pretty flawed.

If anything you should be using DTS, COM, setup a linked server to a file, do a bulk insert first to get your data, or preferably pass it in from your application.

The Text data type allows more than 8000 characters. And that is what you should be using for this kind of data.

The only issue I see with this is there seems to be no way to edit the recordset. Being able to do an update on this data source would be supremely useful.

» Report offensive content

Hi Arthur,

really good to see ur code for reading an xml file and then using it in OPENXML method.

But I am facing a problem here. I used your code to fill a variable with the data of an xml file. It got filled with all the data successfully. But when I used this variable in sp_xml_preparedocument method it doesn't returned any result.

Now I tried the other way. I copied all the data of the xml file and hardcoded that data into a @doc variable, inspiite of using your code to read the xml file. Now OPENXML worked perfectly and it returned records as expected.

I am using element centric mapping...

Can u suggest... I am attaching the code here...

/*** CODE TO READ XML FILE AND USE OPENXML METHOD ***/

/**************************************************/



declare @idoc int

declare @doc varchar(1000)

DECLARE @FileName varchar(255)

DECLARE @ExecCmd VARCHAR(255)

DECLARE @y INT

DECLARE @x INT

DECLARE @FileContents VARCHAR(8000)



CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))



SET @FileName = 'C:\Stu.xml'

SET @ExecCmd = 'type ' + @FileName

SET @FileContents = ''



INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd

SELECT @y = count(*) from #tempXML



SET @x = 0

WHILE @x <> @y

    BEGIN

        SET @x = @x + 1

	SELECT @FileContents = @FileContents + ThisLine + char(10) from #tempXML WHERE PK = @x



    END



print @FileContents

DROP TABLE #tempXML



exec sp_xml_preparedocument @idoc output , @doc



select * from 

OPENXML

(

	@idoc , '/ROOT/student' , 2

)

WITH

(

	id varchar(10),

     	name varchar(10)

)



exec sp_xml_removedocument @idoc





/*** XML FILE DATA  **/

/**************************************************/

<?xml version ="1.0" ?>

<ROOT>

<student>

<id>1</id>

<name>Deepak</name>

</student>

<student>

<id>2</id>

<name>Mahinder</name>

</student>

<student>

<id>3</id>

<name>Gokul</name>

</student>

<student>

<id>4</id>

<name>Raj</name>

</student>

<student>

<id>5</id>

<name>Ashok</name>

</student>

</ROOT>'

» Report offensive content

Hi i have used above piece of code to read an XML file but i didn't get any output. could anyone provide me a solution.

I have an xml file generated by a dataset (ds.ReadXML()). i want insert all these rows into DB. Please suggest me in this regards.

» Report offensive content

I am readinf my xml file the you have described here. But when the xml data is saved as text it "n++" prefix is being added to it. Hence the next operation of sp_xml_preparedocument throws xml parser error. I have attched xml format here.
could you please suggest something?

<?xml version="1.0" encoding="utf-8"?>

<Searches>

  <Search ID="1" SearchCondition="" ElementID="14" SearchCriteria="equals" SearchValue="1/3/2008" />

  <Search SearchCondition="OR" SearchValue="" SearchCriteria="Yes" ID="2" ElementID="6" />

  <Search SearchCondition="OR" SearchValue="HTML" ID="3" SearchCriteria="equals" ElementID="1" />

</Searches>

» Report offensive content

This is really a brilliant piece of work. I can't tell you how many times I get this sort of question. If I may make one suggestion, a lot of XML files have a newline character at the end, which might get convereted to a NULL when you import the results into a table. NULL plus anything is NULL. Pursuant, you might want to do this as you build the string:

Great work! Thank you for sharing!!

SELECT @FileContents = @FileContents + IsNull(ThisLine, '') FROM #tempXML WHERE PK = @x

» Report offensive content

Reading a file into SQL Server... http://whereclause.com/blog/?p=83 ...
Consuming XML in a stored procedure... http://whereclause.com/blog/?p=8 ...

» Report offensive content