Change attribute value in stored procedure

Can someone show me the syntax (if it is possible) to modify an attribute value in a stored proc? For example if I have an element that is being passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to change the 2 to something else (like a 3) depending on a condition? Or do I have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to change the value? Will someone
please let me know? Many Thanks!
There's no XML manipulation functionality built into SQL Server 2000 (other
than shredding the XML into a table, performing your update, and retrieving
it back out as XML again using a FOR XML query - definitely not an efficient
approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
better performing this kind of logic in the client application before
submitting it to the stored procedure (apply a style sheet or use an XML API
like the DOM or the XmlDocument in .NET).
The next release of SQL Server includes some extensions to XQuery that allow
you to modify data within an XML value, which is great if you can wait until
then, but probably of no practical use to you at the moment - sorry!
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
Can someone show me the syntax (if it is possible) to modify an attribute
value in a stored proc? For example if I have an element that is being
passed in as text like
<CustomerName id="2" /> Is there SQLXML functionality that I can use to
change the 2 to something else (like a 3) depending on a condition? Or do I
have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
change the value? Will someone please let me know? Many Thanks!
|||Thanks Graeme! That is exactly what I was looking for.
"Graeme Malcolm" wrote:

> There's no XML manipulation functionality built into SQL Server 2000 (other
> than shredding the XML into a table, performing your update, and retrieving
> it back out as XML again using a FOR XML query - definitely not an efficient
> approach!) You could use SUBSTRING as you suggest, but to be honest you'd be
> better performing this kind of logic in the client application before
> submitting it to the stored procedure (apply a style sheet or use an XML API
> like the DOM or the XmlDocument in .NET).
> The next release of SQL Server includes some extensions to XQuery that allow
> you to modify data within an XML value, which is great if you can wait until
> then, but probably of no practical use to you at the moment - sorry!
> Cheers,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
>
> "vonbrownz" <vonbrownz@.discussions.microsoft.com> wrote in message
> news:62DFF8F0-CF80-46E6-8F42-673D8C0E3BFA@.microsoft.com...
> Can someone show me the syntax (if it is possible) to modify an attribute
> value in a stored proc? For example if I have an element that is being
> passed in as text like
> <CustomerName id="2" /> Is there SQLXML functionality that I can use to
> change the 2 to something else (like a 3) depending on a condition? Or do I
> have to use the built in SQL String parsing funtions (e.g. SUBSTRING) to
> change the value? Will someone please let me know? Many Thanks!
>
>