Re: using OpenXML in T-sql?
From: Ed (anonymous_at_discussions.microsoft.com)
Date: 06/25/04
- Next message: Steve Kass: "Re: Too many unique fields to keep data distinct"
- Previous message: S Taylor: "Re: Too many unique fields to keep data distinct"
- In reply to: Tom Moreau: "Re: using OpenXML in T-sql?"
- Next in thread: Ed: "Re: using OpenXML in T-sql?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 24 Jun 2004 21:28:31 -0700
Thank you for setting me straight on this. I was starting
to stress because I thought maybe there was some magic
syntax (like the brackets around [Value]). I admit that I
am not the most senior person in sql server programming,
especially xml stuff. But the solution in the article I
copied here seemed real cool if I could pass just an xml
style string in place of an array without the additional
stuff. No such luck, heh?
As for my subsciption to professional journals list goes,
it is growing :).
Ed
>-----Original Message-----
>Based on what I see, he didn't represent the true
picture. What I see here
>are code snippets and not a complete set of working
code. The @iDoc was
>never populated, so the code doesn't have a hope of
working.
>
>You may want to check out SQL Pro at:
>
>www.pinnaclepublishing.com/sql
>
>
>--
> 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:215ad01c45a28$3f18fb50$a001280a@phx.gbl...
>Thanks. I did read your other article on MSDN. Very
>insightful. However, of the journals I subscribe to (Sql
>Server Solutions from elementKjournals) I currently do not
>have a subscription with Sql Server Professional and thus
>cannot access your article there.
>
>While I'm at it, here is the entire article I was
>referring too (actually, just a tip) at the beginning of
>my post. The author makes a reference to using comma
>delimited strings and opts for the xml string instead. I
>just couldn't follow the solution, but it sounds pretty
>nifty. My confusion is if the guy inadvertently left out
>
>sp_xml_preparedocument
>
>or he is actually not using it. Here is his whole tip.
>
>>>
>ADVISOR TIP
>SQL Server and Arrays
>Explore these workarounds to a common problem.
>
>By Russ Nemhauser
>
>
>One of the most frequent questions I receive about SQL
>Server development has to do with passing an array (or
>collection) of values to a stored procedure as an input
>parameter. Quite often, I've had to return rows from a
>table based on selections the user made. There's obviously
>a performance hit if you retrieve one row at a time
>because you can only specify the row's ID.
>
>Developers have discovered a few workarounds for cases
>like this. Some create a slew of input parameters with
>default values (which makes them optional):
>
>CREATE PROCEDURE GetRecords
> @ID1 INT,
> @ID2 INT,
> @ID3 INT,
> @ID4 INT
>AS ...
>
>With an approach like this, you're betting you'll never
>have to specify more values than the number of input
>parameters you create. In addition, inserting all these
>input parameters into a temporary table can cause a
>noticeable decrease in performance if you're dealing with
>a lot of values.
>Another approach is to create one large VARCHAR input
>parameter, and then attempt to parse it:
>
>CREATE PROCEDURE GetRecords
> @Params VARCHAR(8000)
>AS ...
>
>Your stored procedure will have to retrieve the values
>from the @Params parameter and insert them into a
>temporary table so you can do things such as joins or work
>with the IN statement. You can separate each value in the
>parameter by a comma or other delimiter:
>
>EXEC GetRecords '4,19,42'
>
>One problem with this approach is T-SQL isn't a
>programming language. It's a data access language, and
>working with strings isn't necessarily ideal-especially
>where performance is concerned.
>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)
>
>Now that the values exist in the temporary table, you can
>use them in any way you'd use values in any table.
>Furthermore, you can create the temporary table manually
>using the CREATE TABLE statement if you have to get fancy
>with indexes or keys.
><<
>
>
>
>
>
>>-----Original Message-----
>>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: Steve Kass: "Re: Too many unique fields to keep data distinct"
- Previous message: S Taylor: "Re: Too many unique fields to keep data distinct"
- In reply to: Tom Moreau: "Re: using OpenXML in T-sql?"
- Next in thread: Ed: "Re: using OpenXML in T-sql?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
