MS SQL for XML query

Question

Need Some Help.

Currently have 2 tables.

#head

PARAM1 PARAM2 PARAM3
------ ------ ------
AAA    BBB    CCC

and

#body

NAME                 SEX                  EMAIL
-------------------- -------------------- --------------------
Tania                Female               [email protected]
Sergey               male                 [email protected]

By using next query I am getting next result:

QUERY:

SELECT
        PARAM1,
        PARAM2,
        PARAM3,
        (SELECT
            NAME,
            SEX,
            EMAIL
        FROM #body
        FOR XML RAW('body'),ELEMENTS,TYPE
        ) AS bodyS
    FROM #head as HEAD
    FOR XML AUTO

Current Result:

<HEAD PARAM1="AAA" PARAM2="BBB" PARAM3="CCC">
  <bodyS>
    <body>
      <NAME>Tania</NAME>
      <SEX>Female</SEX>
      <EMAIL>[email protected]</EMAIL>
    </body>
    <body>
      <NAME>Sergey</NAME>
      <SEX>male</SEX>
      <EMAIL>[email protected]</EMAIL>
    </body>
  </bodyS>
</HEAD>

Expected result:

<HEAD PARAM1="AAA" PARAM2="BBB" PARAM3="CCC">
    <body>
      <NAME>Tania</NAME>
      <SEX>Female</SEX>
      <EMAIL>[email protected]</EMAIL>
    </body>
    <body>
      <NAME>Sergey</NAME>
      <SEX>male</SEX>
      <EMAIL>[email protected]</EMAIL>
    </body>
</HEAD>

Could somebody help Me to figure-out how can I reach expected result.

Thanks to everyone in advance for any help !

Fabio · Accepted Answer · 2016-08-10 09:09:20Z

1

Using @ in the column name will create a attribute instead of element.
Use sub query with FOR XML PATH for creating "internal" elements from returned rows.

SELECT 
    'AAA' AS '@Param1'
    , 'BBB' AS '@Param2'
    , 'CCC' AS '@Param3'
    , (SELECT Name, Sex, Email FROM [yourTable] FOR XML PATH ('BODY'), TYPE)
FOR XML PATH ('HEAD')

edited Aug 10, 2016 at 9:09

answered Aug 10, 2016 at 8:30

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Firmack Bendera Over a year ago

Ruery Result: <HEAD Param1="AAA" Param2="BBB" Param3="CCC"><BODY><Name>Tania</Name><Sex>Female</Sex><Email>[email protected]</Email></BODY><BODY><Name>Sergey</Name><Sex>male</Sex><Email>[email protected]</Email></BODY></HEAD> not really what I am looking for. But anyway, thanks.

Fabio Over a year ago

@FirmackBendera, sorry the TYPE parameter was missed. Check updated query

Firmack Bendera Over a year ago

Thanks A lot ! Works as expected ! =)

Collectives™ on Stack Overflow

MS SQL for XML query

Thanks to everyone in advance for any help !

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Thanks to everyone in advance for any help !

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related