none
Using For XML Auto ?

    Question

  • Hi everyone,

    I am trying to use For XML Auto, to convert a row data of table into xml format.

    I used below code: select * from myTable  where id  = 5 for xml auto, elements

    And I got the result like this:

    <myTable><id>5</id><name>John</name></myTable>

    But I am wanting the result include the tag: <DocumentElement> and myTable is upper case like below:

    <DocumentElement><MyTable><Id>5</Id><Name>John</Name></MyTable><DocumentElement>

    Are there anyone know how to do that ?

    Thank you very much !

    Thursday, October 17, 2013 3:20 AM

Answers

  • Alias the table name and column names to whatever you want to be the tags and specify the ROOT directive.

    select id as Id, name as Name from myTable as MyTable where id  = 5 for xml auto, Root('DocumentElement'), elements

    Tom

    • Proposed as answer by Latheesh NKMVP Thursday, October 17, 2013 4:50 AM
    • Marked as answer by Taibc Thursday, October 17, 2013 7:06 AM
    • Unmarked as answer by Taibc Tuesday, October 29, 2013 2:24 AM
    • Marked as answer by Taibc Wednesday, October 30, 2013 2:44 AM
    Thursday, October 17, 2013 3:58 AM

All replies

  • Alias the table name and column names to whatever you want to be the tags and specify the ROOT directive.

    select id as Id, name as Name from myTable as MyTable where id  = 5 for xml auto, Root('DocumentElement'), elements

    Tom

    • Proposed as answer by Latheesh NKMVP Thursday, October 17, 2013 4:50 AM
    • Marked as answer by Taibc Thursday, October 17, 2013 7:06 AM
    • Unmarked as answer by Taibc Tuesday, October 29, 2013 2:24 AM
    • Marked as answer by Taibc Wednesday, October 30, 2013 2:44 AM
    Thursday, October 17, 2013 3:58 AM
  • Thanks Tom,

    It is ok now.

    Thursday, October 17, 2013 7:08 AM
  • Alias the table name and column names to whatever you want to be the tags and specify the ROOT directive.

    select id as Id, name as Name from myTable as MyTable where id  = 5 for xml auto, Root('DocumentElement'), elements

    Tom

    Hi Tom,

    May I ask you one more question:

    When I use xml auto, float fields are stored as incorrect format:

    Such as: <Area>2.130000000000000e+002</Area>            (expected value is : 213)

    How I can fix this ?

    Thanks and regards,

    Tai

    Tuesday, October 29, 2013 2:24 AM
  • You can cast the float as a different datatype, for example, decimal

    select id as Id, name as Name,
       Cast(Area As decimal(8,2)) As Area 
    from myTable as MyTable 
    where id  = 5 
    for xml auto, Root('DocumentElement'), elements 
    Tom

    Tuesday, October 29, 2013 4:13 AM
  • You can cast the float as a different datatype, for example, decimal

    select id as Id, name as Name,
       Cast(Area As decimal(8,2)) As Area 
    from myTable as MyTable 
    where id  = 5 
    for xml auto, Root('DocumentElement'), elements 
    Tom

    Thanks Tom,

    But, if I use decimal (8,1), the value 123 will store as 123.0. I am want: 123 will store as 123 and 123.5 will store as 123.5.

    How can I do that ?

    Kind regards,

    Tai

    Tuesday, October 29, 2013 7:50 AM