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

Huava

New Member
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. \[code\]Firstname Lastname class markJohn Lim V 60Wills smith V 80Mark Bay V 75Carl mark V 65\[/code\]and this is my SQL XML statements\[code\] Select Firstname, Lastname, class, mark from Student for xml path('Students'), type\[/code\]The result as below\[code\]<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>\[/code\]I want the below result were class and mark tag are enclosed in another tag:\[code\] <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>\[/code\]Any idea how to go about doing this? Thanks!
 
Back
Top