Retrieve nested parent child xml based on parent xml in sql xml

Question

i have the following codes in SQL XML. I need to insert a parent element into a parent element in SQL XML.

i have the following table.

Firstname   Lastname    class   mark
John        Lim          V      60
Wills       smith        V      80
Mark        Bay          V      75
Carl        mark         V      65

and this is my SQL XML statements

 Select 
  Firstname,
  Lastname,
  class,
  mark
  from Student for xml path('Students'), type

The result as below

<Students>
  <Firstname>John</Firstname>
  <Lastname>Lim</Lastname>
  <class>V</class>
  <mark>60</mark>
</Students>
<Students>
  <Firstname>Wills</Firstname>
  <Lastname>smith</Lastname>
  <class>V</class>
  <mark>80</mark>
</Students>
<Students>
  <Firstname>Mark</Firstname>
  <Lastname>Bay</Lastname>
  <class>V</class>
  <mark>75</mark>
</Students>
<Students>
  <Firstname>Carl</Firstname>
  <Lastname>mark</Lastname>
  <class>V</class>
  <mark>65</mark>
</Students>

I want the below result were class and mark tag are enclosed in another tag:

    <Students>
  <Firstname>John</Firstname>
  <Lastname>Lim</Lastname>
  <Details>
    <class>V</class>
    <mark>60</mark>
  </Details>  
</Students>
<Students>
  <Firstname>Wills</Firstname>
  <Lastname>smith</Lastname>
  <Details>
    <class>V</class>
    <mark>80</mark>
  </Details>
</Students>
<Students>
  <Firstname>Mark</Firstname>
  <Lastname>Bay</Lastname>
  <Details>
    <class>V</class>
    <mark>75</mark>
  </Details>
</Students>
<Students>
  <Firstname>Carl</Firstname>
  <Lastname>mark</Lastname>
  <Details>
    <class>V</class>
    <mark>65</mark>
  </Details>
</Students>

Any idea how to go about doing this?

Thanks!

Mikael Eriksson · Accepted Answer · 2012-06-21 07:38:00Z

2

You can use a column alias.

select Firstname,
       Lastname,
       class as "Details/class",
       mark as "Details/mark"
from Student
for xml path('Students'), type

answered Jun 21, 2012 at 7:38

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Collectives™ on Stack Overflow

Retrieve nested parent child xml based on parent xml in sql xml

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related