using OpenXML in Store Procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

using OpenXML in Store Procedure

http://www.sql-server-performance.com/jb_openxml.asp I need to attribute in one variavel the value of node, for write in column of table. I tried SELECT @variable FROM OPENXML (@xmlHandle, ‘/books/book’, 2) WITH
(
authorVARCHAR(20) ‘./author’,
titleVARCHAR(50) ‘./title’
)
and SELECT * FROM OPENXML (@xmlHandle, ‘/books/book’, 2) WITH
(
@variable = authorVARCHAR(20) ‘./author’,
titleVARCHAR(50) ‘./title’
) DECLARE @xmlDoc VARCHAR(8000)
DECLARE @xmlHandle INT
BEGIN
SET @xmlDoc =

<books>
<book>
<author>Don Box</author>
<title>Essential .NET</title>
</book>
<book>
<author>Ken Henderson</author>
<title>Guru Guide To SQL Server</title>
</book>
</books>
‘ EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc
SELECT * FROM OPENXML (@xmlHandle, ‘/books/book’, 2) WITH
(
authorVARCHAR(20) ‘./author’,
titleVARCHAR(50) ‘./title’
) — Important to specify this
EXEC sp_xml_removedocument @xmlHandle
END www.macul.eti.br www.primeiramao.com.br
(ad free)
Hi ya, if you want to put a cursor on the results or put them into a variable then you’ll need to insert them into a sql table first (table variable, temp table or a permanent table) ie. insert into table_x( author, title )
select * from openxml(…) Cheers
Twan
Tank’s DECLARE @xmlDoc VARCHAR(8000)
DECLARE @xmlHandle INT SET @xmlDoc = ‘
<books>
<book>
<author>Don Box</author>
<title>Essential .NET</title>
</book>
<book>
<author>Ken Henderson</author>
<title>Guru Guide To SQL Server</title>
</book>
</books>
‘ EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlDoc Declare @Var Varchar(20) SELECT @Var = author FROM OPENXML (@xmlHandle, ‘/books/book’, 2)
WITH
(
author VARCHAR(20) ‘./author’,
title VARCHAR(50) ‘./title’
)
Where Author = ‘Ken Henderson’ Print @Var www.macul.eti.br www.macul.hpg.com.br www.primeiramao.com.br
(ad free)
]]>