Re: using OpenXML in T-sql?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ed (anonymous_at_discussions.microsoft.com)
Date: 06/25/04 

Date: Fri, 25 Jun 2004 10:29:37 -0700

I just tried out your ArrayToXML function. Very Slick!
Indeed!

Thanks for sharing,
Ed

>-----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
>>
>>.
>>
>
>.
>

Relevant Pages

  • Re: using OpenXML in T-sql?
    ... am not the most senior person in sql server programming, ... especially xml stuff. ... style string in place of an array without the additional ... >delimited strings and opts for the xml string instead. ...
    (microsoft.public.sqlserver.programming)
  • Re: using OpenXML in T-sql?
    ... If you subscribe to SQL Server Professional, ... 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. ...
    (microsoft.public.sqlserver.programming)
  • Re: using OpenXML in T-sql?
    ... delimited strings and opts for the xml string instead. ... SQL Server and Arrays ... @ID1 INT, ...
    (microsoft.public.sqlserver.programming)
  • Re: Need a simple database for name and email only
    ... function getMultipleSegments($segment, $xml) ... foreach ... $string = strtr; ... $supportedExtensions = array ( ...
    (alt.php)
  • How can I do SQLXML-like things using ADO.NET?
    ... Must be able to get XML from SQL Server 2000 and ... an ExecuteScalar in the Data Access Layer, putting the result into a string, ... putting that into a string builder and using ds.ReadXml on that. ... 8K on SQL Server 2000? ...
    (microsoft.public.dotnet.framework.adonet)