Returning XML from a stored procedure (SQL2005)

Question

Returning XML from a stored procedure (SQL2005)

sardaukar.siet

Old Hand

Points: 383
More actions
August 22, 2008 at 7:52 am

#191798

Hello all!
I am a total n00b, and I wonder if a return like this:
| ID | Moment | Associated values |
| 1 | 2008-03-26 | [xml here] |
| 2 | 2008-01-04 | [xml here] |
| 3 | 2007-11-02 | [xml here] |
Is possible in a stored procedure or if XML returns must be 100% in XML (using for xml auto, for example). If possible, an example maybe? 😀
Thanks!
edit - the forum ate my XML 😐

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Depends on what you need it for. SQL 2005 has an xml datatype and you could create a temporary table or table variable to put your results in with non-xml columns and 1 xml type column and then return the rest. OR use a correlated subquery or derived table that returns a column that is xml.

Something like:

[font="Courier New"]USE AdventureWorks

GO

SELECT

C.CustomerID,

C.CustomerType,

(SELECT

OrderHeader.CustomerID,

OrderHeader.SalesOrderID,

OrderHeader.Status

FROM

Sales.SalesOrderHeader OrderHeader

WHERE

C.CustomerID = OrderHeader.CustomerID

FOR XML AUTO) AS xml_data

FROM

Sales.Customer C [/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Try replacing every < with < before posting...

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

sardaukar.siet Old Hand Points: 383 More actions · Answer 3

Thanks for all your input! I have found a solution to my problem!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Could you post your solution, if not the one I suggested, so others who may find this thread in a search can benefit?

Thanks,

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sardaukar.siet Old Hand Points: 383 More actions · Answer 5

Sure - but bear in mind: my original reason for posting this was doing this in a single query. My "noobness" might show in the solution I found... 😀

select

S_T.[Id]as Id,

S_T.[Moment]as Moment,

''as Description,

(

select

1as Tag,

NULLas Parent,

s_et.[Code]as [WantedEntity!1!Name],

s_ie.[Entity]as [WantedEntity!1!Value]

from

[SOME_INSTANCE_ENTITY] s_ie

inner join

[SOME_ENTITY_TYPE] s_et

on

( s_ie.[IDType] = s_et.[ID] )

where

S_T.[Id] = s_ie.[IDInstance]

for XML EXPLICIT

) as Entities

from

[SOME_TABLE] S_T

where

[IdPosted] = @Parameter

I substituted the names...

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Looks just like what I proposed except you used XML EXPLICIT.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sardaukar.siet Old Hand Points: 383 More actions · Answer 7

Yes - still, I wanted to have

<top_element>

</top_element>

but no cigar...