Creating XML output...unusual/impossible format?

Hi all,

I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.

The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)

The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.

Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?

I want my SYMBOL tag to be the actual SYMBOL for the stock fund.

Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.

Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!

Thanks!You cannot achieve this with a single SELECT statement because you can have only one column name for the entire result set.(you stated this already). If you need to have the column value as a tag then a stored procedure is the way to go where you can generate your own XML TAGs.
Hi all,

I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.

The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)

The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.

Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?

I want my SYMBOL tag to be the actual SYMBOL for the stock fund.

Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.

Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!

Thanks!|||Thanks for the reply.

I was afraid of that. The previous version of the product used a C++ program to generate the XML (or pseudo-XML) output. I was hoping it was just because the developer didn't know how to use the XML extraction stuff in SQL Server. *sigh*

Oh well, I guess I get to gain some C# practice, as the C++ application has the source of the data "hard coded" in the logic, and we need to use a predefined, "standard" linked server reference (aka "alias").|||"Can't be Done"?

The three words I tell developers to never let me hear, otherwise I bring out the baseball bat

Dude, you know the drill

DDL, DML, I gues we got the expected results already...|||Never mind...but I'm only guessing...

USE Northwind
GO

CREATE TABLE myTable99(FundSymbol char(5), FundName varchar(255))
GO

INSERT INTO myTable99(FundSymbol, FundName)
SELECT 'AMRGX', 'AMERICAN GROWTH D' UNION ALL
SELECT 'AHERX', 'AHERX HERITAGE FUND' UNION ALL
SELECT 'AMRVX', 'AMERICAN INVESTORS GROWTH FUND'

SELECT XML_Output
FROM (
SELECT '<?xml version="1.0" encoding="utf-8"?>' AS XML_Output, 1 AS XML_Group, Null AS FundSymbol
UNION ALL
SELECT '<FUNDS>'AS XML_Output, 2 AS XML_Group, Null AS FundSymbol
UNION ALL
SELECT REPLICATE(' ',20)+'<'+FundSymbol+'>'+CHAR(13)+CHAR(10)
+ REPLICATE(' ',40)+'<NAME>'+FundName+'</NAME>'+CHAR(13)+CHAR(10)
+ REPLICATE(' ',20)+'</'+FundSymbol+'>'+CHAR(13)+CHAR(10)AS XML_Output
, 3 AS XML_Group, FundSymbol
FROM myTable99
UNION ALL
SELECT '<FUNDS>'AS XML_Output, 4 AS XML_Group, Null AS FundSymbol
) AS XXX
ORDER BY XML_Group, FundSymbol
GO

DROP TABLE myTable99
GO|||*sigh* What can I say? Every day you guys amaze me more-n-more :)

That's EXACLY what I was a-lookin for! I appreciate you doing my homework for me *hanging head* I tend to forget the usefullness of UNION, and have yet to use "REPLICATE", so now I need to go look THAT one up in BOL so that I can once again expand my horizons just a little more.

Funny (to me, anyway) that because every other application in our world here that creates XML output uses the FOR XML directive to create the output, that I get tunnel vision and just can't seem to see the easy answer lies in "standard" SQL. I guess kinda like my kids, who can't seem to get their heads around the ideas that (a) you can make your own ice cream, (b)clothes can come from a sewing machine instead of a store, (c) there's a way to change your oil without going to Jiffy Lube, and myriad other such examples of "OMG, you mean you can do that the old-fashioned way?".

But I digress...Thanks once again, Brett, for taking the time and effort to plainly show how I can get around yet another stumbling block.

It's too bad though, that since it can't be done, I will have to disregard your fantasy code and open my C# book. ;)

Thanks again!|||A mere trifle...

I was interested, because of a discussion I had with a Java developer.

If I could deliver the content in a manner like you are looking for, then the development life cycle for java gets cut in half.

I'm just exploring this now, but I was leaning on having metadata stored in the database, where I could configure all of the results via table.

Each element would have it's own properties stored.

So if the don't like the text to be black, I can make it red, on the fly. No application release headaches.

I was going to extend that and see if I could also incorporate the style sheets in the database as well..

FOR XML...what a bunch of crap