Re: using OpenXML in T-sql?
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 06/24/04
- Next message: Michael Tissington: "IF EXISTS before DELETE"
- Previous message: Mike Moore: "Re: SQL Mail"
- In reply to: Ed: "Re: using OpenXML in T-sql?"
- Next in thread: Ed: "Re: using OpenXML in T-sql?"
- Reply: Ed: "Re: using OpenXML in T-sql?"
- Reply: Ed: "Re: using OpenXML in T-sql?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 24 Jun 2004 15:55:20 -0400
I have written an article on passing arrays to stored procs via
comma-delimited strings. If you subscribe to SQL Server Professional, check
out:
http://www.pinpub.com/html/main.isx?sub=64&story=819
Essentially, I create a UDF to translate a comma-delimited string to an XML
doc:
create function dbo.ArrayToXML
(
@InputStr varchar (8000)
, @Delim varchar (5)
)
returns varchar (8000)
as
begin
return ('<ROOT><Worktable Value="'
+ replace (@InputStr
, @Delim
, '"/><Worktable Value="')
+ '"/></ROOT>')
end
go
Regardless, you still need to go with sp_xml_preparedocument and OPENXML()
to make use of it.
-- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "Ed" <anonymous@discussions.microsoft.com> wrote in message news:20b3b01c45a22$297b9790$a601280a@phx.gbl... Yes. I am aware of this. According to the examples in Books Online for OpenXml the examples all called sp_xml_prepareDocument. But in this article, the author did not call sp_xml_prepareDocument. I was just checking if he found a way to not have to do that. The actual article was about passing an array to an SP. He suggests you can pass an xml string to the sp (as opposed to an xml doc - values from array would be in an xml string) and use Openxml to retrieve the values in one call rather than looping through an array. Any thoughts on that? Thanks, Ed >-----Original Message----- >You did not run sp_xml_prepare_document or OPENXML(). Check out: > >http://msdn.microsoft.com/library/default.asp? url=/library/en-us/dnsqlpro01/html/sql01c5.asp > >-- >Tom > >---------------------------------------------------------- ----- >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >SQL Server MVP >Columnist, SQL Server Professional >Toronto, ON Canada >www.pinnaclepublishing.com/sql > > >"Ed" <anonymous@discussions.microsoft.com> wrote in message >news:210d101c45a1a$ffe60250$a101280a@phx.gbl... >Hello, > >I stumble onto an article which explains how you can pass >data to a T-sql statement in the form of an xml string but >I got lost on how the xml string is invoked into the >Select statement. Here is the meat of the article: > >>> >SQL Server 2000 offers you the ability to work with XML in >your stored procedures. If you stick with the concept of >using a large VARCHAR parameter, you can pass in an XML >string that represents the ID numbers with which you have >to work: > ><IDNumbers> > <ID value="4"/> > <ID value="19"/> > <ID value="42"/> ></IDNumbers> > >Using T-SQL's OPENXML, you can insert these values (no >matter how many there are) into a temporary table with >ease: > >DECLARE @iDoc INT >SELECT Value >INTO #tmp >FROM OPENXML(@iDoc, '/IDNumbers/ID', 1) > WITH ([Value] INT) > ><< > >I am thinking like this: > >Declare @xml varchar(8000) >Set @xml='<IDNumbers> > <ID value="4"/> > <ID value="19"/> > <ID value="42"/> ></IDNumbers>' > >So could anyone explain where @xml gets used in the >example from the article above? > >Thanks (again), >Ed > >. >
- Next message: Michael Tissington: "IF EXISTS before DELETE"
- Previous message: Mike Moore: "Re: SQL Mail"
- In reply to: Ed: "Re: using OpenXML in T-sql?"
- Next in thread: Ed: "Re: using OpenXML in T-sql?"
- Reply: Ed: "Re: using OpenXML in T-sql?"
- Reply: Ed: "Re: using OpenXML in T-sql?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
