Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: 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:
    Code:
    <?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:
    Code:
    <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!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    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.
    Quote Originally Posted by TallCowboy0614
    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:
    Code:
    <?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:
    Code:
    <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!

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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").
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    "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...
    Last edited by Brett Kaiser; 09-26-05 at 15:40.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Never mind...but I'm only guessing....

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    *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!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •