Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: Transform XML from a SELECT...FOR XML

    I want to transform elements in a XML-respons.

    If I use FOR XML PATH, ELEMENTS in the SELECT-statment I get the following result:

    <row>
    <nr>99</nr>
    <namn>test bolag</namn>
    </row>
    <row>
    <nr>02</nr>
    <namn>test bolag 2</namn>
    </row>

    I want to modify the elements so that they look like this insteed in the SELECT-statment.

    <row>
    <field name="nr">99</field>
    <field name="namn">test bolag</field>
    </row>
    <row>
    <field name="nr">98</field>
    <field name="namn">test bolag 2</field>
    </row>

    I need to transform the resulting XML because I need to send it to a webservice with a mySQL database on the reciving end.

    Please help me :-)

  2. #2
    Join Date
    Jul 2007
    Posts
    96
    Could you please provide the query you are currently using?

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    Of course...

    SELECT foretag_nkl as nr, foretag as namn FROM dim_foretag
    FOM XML PATH, ELEMENTS

  4. #4
    Join Date
    Jul 2007
    Posts
    96
    The following article should be able to answer your question. Please let us know of how it went

    http://www.sqlservercentral.com/arti...K5+-+XML/3022/

  5. #5
    Join Date
    Dec 2003
    Posts
    7
    Thanks for the article, it will be useful in some other questions I had :-)

    But it didn't helped me to solve this particular problem. The elements are static for each item, that is <field name="nr"> has the same value for each row in the respons.

    If I try to use ="nr" I get an error:

    "Column name 'field = "nr"' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault."

    Space, = or " are invalid in the XML output...

  6. #6
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    Hi, try with
    FOR XML RAW

    Bye

Posting Permissions

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