Formatting/Returning stored proc results as XML

I have a stored proc that contains a simple select statement. The select statement does not use the 'FOR XML' clause, nor can the select statement be modified to use the 'FOR XML' clause (for compatibility reasons)

Is there a way to execute the stored proc such that it returns its output in XML? I'm looking for something similar to this:

Code Snippet

exec spGetUserDetails @.ID = 1234 for xml raw

Thanks.

I found an approach that would preserve backwards compatibility while allowing me to manipulate results retrieved by a stored proc. It has to do with Table-valued user-defined functions:

Move the select statement from the stored proc into a user--defined function that returns TABLE:

Code Snippet

-- Example from Books Online

CREATE FUNCTION fn_SalesByStore (@.storeid int)
RETURNS TABLE
AS
RETURN ( SELECT * from Products where StoedID = @.storeid );

Modify the original stored proc to call this user-defined function, i.e., the stored proc wraps the UDF hence preserving backwards compatibility:

Code Snippet

-- Inside the stored proc comment the select statement and call the UDF

-- SELECT * from Products where StoedID = @.storeid -- No longer needed

select * from fn_SalesByStore (@.storeid);

Now I can call the UDF to retreive the same results that would have been retrieved by the original stroed proc, and more importantly, manipulate them as required.

Code Snippet

select * from fn_SalesByStore (@.storeid) for xml raw

Yazan

Formatting data with FOR XML and sp_makewebtask?

Hi all,

I have a question about using FOR XML and sp_makewebtask. This (see below) is a simplified example where I have a table with 3 columns, the two first columns hold varius data and the third column (Subdata3) holds duplicated data.

My question is: Is it possible to transform the hierachy of data (see "Prefered XML output" for details) when transforming from SQL to XML using either FOR XML EXPLICIT or by using some sort of more advanced sp_makewebtask template (or some other technique perhaps)? And if, how? Any help welcome!

Cheers,
Christian

-------

-- sp_makewebtask --
EXEC sp_makewebtask
@.outputfile = c:\temp\output.xml,
@.query = 'SELECT Subdata1, Subdata2, Subdata3 FROM Data WHERE Subdata3 = X FOR XML AUTO, ELEMENTS',
@.templatefile ='c:\temp\template.tpl'

-- c:\temp\template.tpl --
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</Data>

-- c:\temp\output.xml --
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
</Data>

-- Prefered XML output --
<?xml version="1.0" encoding="UTF-8"?>
<Data Subdata3="1">
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
</Subdata>
</Data>Nevermind, solved it using FOR XML EXPLICIT. :D

/Christian

Formating XML field in report

Hi:
The database we are reporting from (DB2 running on AS/400) has a field with
the data type of CLOB. We would like to perform an XSLT on this field within
the same report that contains more "normal" data (strings, numeric, etc.)
types.
This database contains error and status messages that are generated as
various processes are run. We would like to be able to present the CLOB data
on the report based on the type of process that produced the data. In other
words, if process A produced the entery in to the database, on the report for
process A, format the CLOB data this way, if process B, then the CLOB data is
represented in another way. The data in the CLOB is XML and each process has
its own schema.
Right now I am stumped. Is this possible? Can you point me in the right
direction?
Thanks.You're probably going to need to write a custom function (or perhaps a
custom assembly if you're going to need to read XSLTs from disk) to do this.
You could pass the data along with the process to your custom function which
would apply the transform and return the string to display in the textbox.
E.g. =Code.CLOBClass.Convert(Fields!CLOBData.Value,Fields!Process.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"bwschiek@.hotmail.com" <bwschiekhotmailcom@.discussions.microsoft.com> wrote
in message news:95B8ADFD-2A23-4DD5-A039-971AC84B5AF9@.microsoft.com...
> Hi:
> The database we are reporting from (DB2 running on AS/400) has a field
with
> the data type of CLOB. We would like to perform an XSLT on this field
within
> the same report that contains more "normal" data (strings, numeric, etc.)
> types.
> This database contains error and status messages that are generated as
> various processes are run. We would like to be able to present the CLOB
data
> on the report based on the type of process that produced the data. In
other
> words, if process A produced the entery in to the database, on the report
for
> process A, format the CLOB data this way, if process B, then the CLOB data
is
> represented in another way. The data in the CLOB is XML and each process
has
> its own schema.
> Right now I am stumped. Is this possible? Can you point me in the right
> direction?
> Thanks.

Format Table Row into XML and Insert into Another Table?

First I am using SQL Server 2000 (I think this is easy to do in 2005)

I am trying to do the following:

- Format a table row into an XML string

- Insert the XML string into another table.

Here is what I want to do in a nutshell:

insert into Table1(xml_data)

select * from customers

where customerid = 'alfki'

for xml auto, elements

However, I get the following error: The FOR XML clause is not allowed in a INSERT statement.

I tried a few other formats, such as User Defined Function and etc.

Can this be done? Any ideas?

R

This is not possible in SQL Server 2000 without going through lot of pain on the server-side. So best is to get the XML data to the client and then insert from there.

Format output of FOR XML RAW

Hi! I use SELECT FOR XML RAW on MSSQL 2000.
I would like to be able to format the XML output so that instead of
having:
<row a="asd1"...>
<row a="asd2"...>
<row a="asd3"...>
I would get
<1 a="asd1"...>
<2 a="asd2"...>
<3 a="asd3"...>
or better yet be able to assign a specifc column value to replace
"row".
This is because I want to load the XML stream into LUA tables, and LUA
tables need unique indexes.
Thanks in advance.
Jeff Lambert
RAW mode in SQL2K generates elements with name 'row'. If you want to change
element name (custom name) you have to use EXPLICIT or AUTO (using aliases)
mode. In SQL 2005 you are able to give custom name with RAW clause.
/Patrick
"Jeff Lambert" <xradicalx@.gmail.com> wrote in message
news:f4f18e47.0410051032.ca9317d@.posting.google.co m...
> Hi! I use SELECT FOR XML RAW on MSSQL 2000.
> I would like to be able to format the XML output so that instead of
> having:
> <row a="asd1"...>
> <row a="asd2"...>
> <row a="asd3"...>
> I would get
> <1 a="asd1"...>
> <2 a="asd2"...>
> <3 a="asd3"...>
> or better yet be able to assign a specifc column value to replace
> "row".
> This is because I want to load the XML stream into LUA tables, and LUA
> tables need unique indexes.
> Thanks in advance.
> Jeff Lambert
|||Also note that <1 a="asd1"/> is not a well-formed XML element (names cannot
start with numbers).
If you want to generate something like <row1/><row2/> etc. you would need an
XSLT stylesheet that creates such elements and copies all the attributes
over.
What is LUA exactly?
Best regards
Michael
"Patrick Akerblom" <patrik.akerblom@.ontrax.se> wrote in message
news:%23m2N2x2qEHA.2900@.TK2MSFTNGP12.phx.gbl...
> RAW mode in SQL2K generates elements with name 'row'. If you want to
> change element name (custom name) you have to use EXPLICIT or AUTO (using
> aliases) mode. In SQL 2005 you are able to give custom name with RAW
> clause.
> /Patrick
> "Jeff Lambert" <xradicalx@.gmail.com> wrote in message
> news:f4f18e47.0410051032.ca9317d@.posting.google.co m...
>

format of for xml auto

I am using sql server 2000 and want to know how to get xml out of the database that looks like this using for xml auto

<Clients>
<Client ID="1">
<Employer="Company1" />
<Employer="Company2" />
<Contact type="phone">
<contact type="email" value="test@.test.com">
<contact type="phone" value="555-5555">
</Client>
<Client ID="2">
<Employer="Company3" />
<Employer="Company4" />
<Contact type="phone">
<contact type="email" value="test@.test.com">
<contact type="phone" value="555-5555">
</Client>
</Clients>

The problem I am having is that Contact is nested inside employer when I select Employer before Contact and the opposite happens when I select Contact first. They both join to the Client table so I would assume they both should nest directly under Client. How do I get different fields to nest directly under the same element like above?

Show me your SQL so I can offer suggestions on how to change it.

Mark

|||

I was trying to create two different child elements under a parent. I figured out a way to do it using for xml explicit. If anyone else has a similiar need, here is a code sample I made for the Northwind database

SELECT

1

as Tag,nullas Parent,

c

.CustomerIDAs [c!1!CustomerID],

c

.ContactNameAs [c!1!CustomerName],NULLAs [cd!2!CustomerTypeID],NULLAs [o!3!OrderID]

FROM

customers c

UNION

SELECT

2

as Tag,

1

as Parent,

c

.CustomerID,NULL,

cd

.CustomerTypeID,NULL

FROM

customers c

left

join CustomerCustomerDemo cdon cd.customerid= c.customerID

UNION

SELECT

3

As Tag,

1

As Parent,

c

.CustomerID,NULL,NULL,

o

.OrderID

FROM

Customers c

join

Orders oon o.customerID= c.CustomerID

ORDER

BY CustomerID

for

xmlexplicit