Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Hi All,

I have a xml column in a table. As part of converting a XML into rowset i wrote a small proc like;

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

DECLARE @.DocHandle int

DECLARE @.XmlDocument nvarchar(1000)

SET @.XmlDocument = N'<XMLDATA>

<COLUMNS>

<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>

<Column name="Val2"> 2 </Column>

<Column name="Val3"> 3 </Column>

<Column name="Val4"> Test0 </Column>

<Column name="Val5"> Test1 </Column>

<Column name="Val6"> Test2 </Column>

<Column name="Val7"> Test3 </Column>

<Column name="Val8"> Test4 </Column>

</COLUMNS>

</XMLDATA>'

-- Create an internal representation of the XML document.

EXECsp_xml_preparedocument @.DocHandle OUTPUT, @.XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT*

FROMOPENXML(@.DocHandle,'/XMLDATA/COLUMNS/Column',3)

WITH(Header varchar(50)'@.name',

Val varchar(50)'text()')

EXECsp_xml_removedocument @.DocHandle

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

This works fine, but my query is how to modify this proc to read the XML from a table rather than hard code it in the proc itself.

Thanks in Advance

Since you mention xml column, so I assume you are using sql 2005. To process xml column in table especially you might have > 1 rows of xml data, you want to use xml nodes() method. For you example:

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
go