Multiple rows are not showing when we convert the table data into XML in SQL <p>I have two table as given below.</p> <p><code>OrderHeader</code>:</p> <pre><code>PKOrderHeader CustomerCode DocumentRef SiteCode 1 JOE TEST1 TH 2 POL TEST2 CO 3 GEO TEST3 KH </code></pre> <p><code>OrderDetails</code>:</p> <pre><code>FKOrderHeader ProductCode RotationLineNo 1 PRD1 1 1 PRD2 2 2 PRD3 2 3 PRD4 3 </code></pre> <p>I need to get the XML string as below after converting the table data as XML string</p> <pre><code>&lt;ORDERS&gt; &lt;SO&gt; &lt;HD&gt; &lt;PKOrderHeader&gt;1&lt;/PKOrderHeader&gt; &lt;CustomerCode&gt;JOE&lt;/CustomerCode&gt; &lt;/HD&gt; &lt;HO&gt; &lt;DocumentRef&gt;TEST1&lt;/DocumentRef&gt; &lt;SiteCode&gt;TH&lt;/SiteCode&gt; &lt;/HO&gt; &lt;LO&gt; &lt;FKOrderHeader&gt;1&lt;/FKOrderHeader&gt; &lt;ProductCode&gt;PRD1&lt;/ProductCode&gt; &lt;RotationLineNo&gt;1&lt;/RotationLineNo&gt; &lt;/LO&gt; &lt;LO&gt; &lt;FKOrderHeader&gt;1&lt;/FKOrderHeader&gt; &lt;ProductCode&gt;PRD2&lt;/ProductCode&gt; &lt;RotationLineNo&gt;2&lt;/RotationLineNo&gt; &lt;/LO&gt; &lt;/SO&gt; &lt;SO&gt; &lt;HD&gt; &lt;PKOrderHeader&gt;2&lt;/PKOrderHeader&gt; &lt;CustomerCode&gt;POL&lt;/CustomerCode&gt; &lt;/HD&gt; &lt;HO&gt; &lt;DocumentRef&gt;TEST2&lt;/DocumentRef&gt; &lt;SiteCode&gt;CO&lt;/SiteCode&gt; &lt;/HO&gt; &lt;LO&gt; &lt;FKOrderHeader&gt;2&lt;/FKOrderHeader&gt; &lt;ProductCode&gt;PRD2&lt;/ProductCode&gt; &lt;RotationLineNo&gt;2&lt;/RotationLineNo&gt; &lt;/LO&gt; &lt;/SO&gt; &lt;SO&gt; &lt;HD&gt; &lt;PKOrderHeader&gt;3&lt;/PKOrderHeader&gt; &lt;CustomerCode&gt;GOE&lt;/CustomerCode&gt; &lt;/HD&gt; &lt;HO&gt; &lt;DocumentRef&gt;TEST3&lt;/DocumentRef&gt; &lt;SiteCode&gt;KH&lt;/SiteCode&gt; &lt;/HO&gt; &lt;LO&gt; &lt;FKOrderHeader&gt;3&lt;/FKOrderHeader&gt; &lt;ProductCode&gt;PRD3&lt;/ProductCode&gt; &lt;RotationLineNo&gt;3&lt;/RotationLineNo&gt; &lt;/LO&gt; &lt;/SO&gt; &lt;/ORDERS&gt; </code></pre> <p>The query that I used to generate the XML string is as given</p> <pre><code> SELECT (SELECT PKOrderHeader, CustomerCode FROM #OrderHeader FOR XML PATH(''), TYPE) AS HD, (SELECT DocumentRef, SiteCode FROM #OrderHeader FOR XML PATH(''), TYPE) AS HO, (SELECT FKOrderHeader, ProductCode, RotationLineNo FROM #OrderDetail FOR XML PATH(''), TYPE) AS LO FOR XML PATH('SO'), ROOT('ORDERS') </code></pre> <p>But when I generated the XML string I am getting only the single rows data as XML string as liken given below. Also the LO section is also not showing the multiple rows.</p> <pre><code>&lt;ORDERS&gt; &lt;SO&gt; &lt;HD&gt; &lt;PKOrderHeader&gt;1&lt;/PKOrderHeader&gt; &lt;CustomerCode&gt;JOE&lt;/CustomerCode&gt; &lt;/HD&gt; &lt;HO&gt; &lt;DocumentRef&gt;TEST1&lt;/DocumentRef&gt; &lt;SiteCode&gt;TH&lt;/SiteCode&gt; &lt;/HO&gt; &lt;LO&gt; &lt;FKOrderHeader&gt;1&lt;/FKOrderHeader&gt; &lt;ProductCode&gt;PRD1&lt;/ProductCode&gt; &lt;RotationLineNo&gt;1&lt;/RotationLineNo&gt; &lt;/LO&gt; &lt;/SO&gt; &lt;/ORDERS&gt; </code></pre> <p>So can anyone help me to get multiple row data as XML string?</p>