SQL Server: Nested Elements with FOR XML PATH

Question

I want to embed each of my XML elements.

Take the following example:

DECLARE @TempTable TABLE ( [Column1] char(10), [Column2] char(10) ); INSERT INTO @TempTable([Column1], [Column2]) VALUES ('some value', 'some value'), ('some value', 'some value'), ('some value', 'some value'), ('some value', 'some value') SELECT ( SELECT * FROM @TempTable FOR XML PATH('Row'), TYPE) FOR XML PATH('ParentRow'), ROOT('Root') 

The following XML will be returned:

  <Root> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> </Root> 

I do not want XML to be formatted. Rather, I want each <Row> element to be wrapped in a <ParentRow> element, as shown below:

  <Root> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> </Root> 

Any help with these guys?

Answer 1

Ok, here is the code.

 DECLARE @TempTable TABLE ( [Column1] char(10), [Column2] char(10) ); INSERT INTO @TempTable([Column1], [Column2]) VALUES ('some value', 'some value'), ('some value', 'some value'), ('some value', 'some value'), ('some value', 'some value') SELECT ( SELECT ( SELECT T1.* FOR XML path('') ,root('Row') ,type ) FROM @TempTable AS T1 FOR XML path('ParentRow') ,type ) FOR XML path('Root') 

and here is the result.

 <Root> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> <ParentRow> <Row> <Column1>some value</Column1> <Column2>some value</Column2> </Row> </ParentRow> </Root> 

good luck ...

Answer 2

 SELECT (SELECT * FROM @TempTable WHERE t.Column1 = Column1 FOR XML PATH('ParentRow'),ROOT('Row'), TYPE) FROM @TempTable t FOR XML PATH(''), ROOT('Root') 

Result

 <Root> <Row> <ParentRow> <Column1>some value1</Column1> <Column2>some value </Column2> </ParentRow> </Row> <Row> <ParentRow> <Column1>some value2</Column1> <Column2>some value </Column2> </ParentRow> </Row> <Row> <ParentRow> <Column1>some value3</Column1> <Column2>some value </Column2> </ParentRow> </Row> <Row> <ParentRow> <Column1>some value4</Column1> <Column2>some value </Column2> </ParentRow> </Row> </Root>