Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Posts
    1

    Question Unanswered: How to generate an XML file from tables?

    Hi experts,

    I am new at SQL I am using MS SQL Server 2005. I would like to know if it is possible to generate an XML file from different tables :
    in the XML I would like to have a segment by line of the table that contains the data of all the columns.
    For example, I have a table tbl_test that have 3 columns : col1, col2, col3.
    In my file I would like to have the segment <detail> that contains the data of the columns per line, ie:

    <detail>
    <col1>data1_1</col1>
    <col2>data1_2</col2>
    <col3>data1_3</col3>
    </detail>

    <detail>
    <col1>data2_1</col1>
    <col2>data2_2</col2>
    <col3>data2_3</col3>
    </detail>

    and so on...

    Is it also possible to add an information at the beginning of the fileaccording to the table the data is extracted from? For example:

    <detail>
    <name>tbl_test</name>
    <col1>data</col1>
    <col2>data</col2>
    <col3>data</col3>
    </detail>

    Does anyone could help me on what would be the best approach to do that?

    Thanks a lot,

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you want to maybe look into using the FOR XML clause of the SELECT statement.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    steer clear of FOR XML EXPLICIT though. it's a mess.

    prefer FOR XML PATH for complex xml structures.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    EXPLICIT is fine for small stuff but bloats quickly.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    i consider FOR XML EXPLICIT an abomination.

    the PM that came up with it deserves a sound spanking by a cave troll.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by jezemine
    the PM that came up with it deserves a sound spanking by a cave troll.
    that will cost you 10 gold pieces and 5 silver pieces.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    got change for a platinum piece?

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yes but utilizing trolls also costs you 10 charisma points.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    no matter. I am already in the negative in that regard.

Posting Permissions

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