Write stored procedure parameters to an XML Column

Question

I have a need to take the parameters passed in to a Stored Procedure (SQL 2005) and write those values into an XML column as one xml document.

Looking for an idea on how to start it.

Denis Valeev · Accepted Answer · 2010-09-07 18:59:57Z

2

Well, let's do this thing!

select 1 [one],2 [two],3 [three]
from  (select null dummy) t
for xml auto

and we get

<t one="1" two="2" three="3" />

Neat, eh?

You can also experiment with for xml path like so:

select 1[one],2[two],3[three]
from  (select null dummy) t
for xml path('foo')

And the result is:

<foo>
  <one>1</one>
  <two>2</two>
  <three>3</three>
</foo>

answered Sep 7, 2010 at 18:59

Denis Valeev

6,02537 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OMG Ponies Over a year ago

+1: Without more detail, the TSQL FOR XML has numerous options to create XML.

tmercer Over a year ago

Awesome. Thanks Denis. Now I'm playing with fire.

Joe Stefanelli Over a year ago

@tmercer: Yes, you are playing with fire, so be careful. See Brent Ozar's blog under the heading "Just Because You Can…." for some great comments on XML in the database.

Collectives™ on Stack Overflow

Write stored procedure parameters to an XML Column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related