Help with XML generation

Question

Post reply

Help with XML generation

Arvind-57714

SSCrazy

Points: 2529
More actions
September 1, 2004 at 12:42 pm

#181855

I need to generate an XML in the below format from my Stored Proc.
<Collateral1><Collateral2><Collateral3>
<Value> 10000</Value>
</Collateral3></Collateral2></Collateral>
the Value is the only column that i get from a table...All the other Collaterals are simply tags that i need to create for the further processing. How can i generate this?
Cheers,

Arvind

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

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

bliviu SSC Veteran Points: 298 More actions · Answer 1

Try this:

select '<Collateral1><Collateral2><Collateral3><Value>'

+ convert(varchar(20), TableValue)

+ '</Value></Collateral3></Collateral2></Collateral1>'

from [Table]

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 2

Cheers m8...I guess that does the trick...but not exactly as i wanted...but i guess this is the best i can get with the time i have on my hands

Cheers,

Arvind

kreynolds Old Hand Points: 307 More actions · Answer 3

you could try this...

select

1 as "Tag"

, null as "Parent"

, '' as "Collateral1!1!!element"

, null as "Collateral2!2!!element"

, null as "Collateral3!3!Value!element"

union all

select

2 as "Tag"

, 1 as "Parent"

, '' as "Collateral1!1!!element"

, '' as "Collateral2!2!!element"

, null as "Collateral3!3!Value!element"

union all

select

3 as "Tag"

, 2 as "Parent"

, '' as "Collateral1!1!!element"

, '' as "Collateral2!2!!element"

, 1000 as "Collateral3!3!Value!element"

for xml explicit

Slavek Rotkiewicz Grasshopper Points: 24 More actions · Answer 4

Are you working on Repo's ?

Maybe we can share some of the data structures ?

Arvind-57714 SSCrazy Points: 2529 More actions · Answer 5

Sorry m8! Not exactly working on repos ...working on generating an xml for the component to parse...

In any case...lemme know if you need any info..maybe I can help.

Cheers!

Arvind

david James Naylor Ten Centuries Points: 1026 More actions · Answer 6

Hi All, I have looked at this discussion for an answer to a problem i have with Stored Procedures and returning XML. I have particularly tried ti implment what Kreynolds said in his thread.

So here is the problem,

I wish to create an XML string that looks something like the following from my stored procedures.

<AudioScriptXML>
<Audio ID=""0"">
<ActorID>1</ActorID>
<SpecialInstructions>Speak in an Irish Accent</SpecialInstructions>
<Script>hello</Script>
</Audio>
<Audio ID=""1"">
<ActorID>1</ActorID>
<SpecialInstructions>Wee bonnie scot talk</SpecialInstructions>
<Script>david</Script>
</Audio>
</AudioScriptXML>

I have the following stored procedure at present.....

CREATE PROCEDURE sp_GetAudioScript

AS

SELECT 1 AS Tag,

null AS Parent,

ID AS [Audio!1!ID],

act AS [Audio!1!ActorID!Element],

auI AS [Audio!1!SpecialInstructions!Element],

ItemValue as [Audio!1!Script!Element]

FROM CustomAudioTable WHERE auS = 1 ORDER BY ID

FOR XML EXPLICIT

GO

However this does not create a root node that opens and the start and closes at the end.

So i am trying this (taken from krynolds example)

CREATE PROCEDURE sp_GetAudioScript

AS

SELECT 1 AS Tag,

null AS Parent

union all

SELECT

2 AS Tag,

1 as Parent,

ID AS [Audio!1!ID],

act AS [Audio!1!ActorID!Element],

auI AS [Audio!1!SpecialInstructions!Element],

ItemValue as [Audio!1!Script!Element]

FROM CustomAudioTable WHERE auS = 1 ORDER BY ID

FOR XML EXPLICIT

GO

and i am told in an erro message the the Column ID is invalid.

I have never used xml within stored procedures before and am a little confused as to how to get the desired results.

Any help would be great.

Thanks, Dave