Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server XML

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 @DocHandleint

DECLARE @XmlDocumentnvarchar(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 @DocHandleOUTPUT, @XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT*

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

WITH(Headervarchar(50)'@name',

Valvarchar(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

[2355 byte] By [MShetty] at [2007-12-24]
«« Context menu item localization
»» Reports for all current active projects
# 1

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

JinghaoLiu at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server XML...
SQL Server
SQL Server XML

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified