Sarah B Sarah B - 5 months ago 5x
SQL Question

XML Output from SQL Server 2008

I am trying to create an XML output from SQL that has 3 nested statements but have pretty minimal experience in this area. The code I've written is below:

select ID as [@ID],
(select cast(Name as int) as [@Name],
(select num as [@Number],
from #tbl_new_claims_export
for xml path('Num'),root('Numbers'), type
from #tbl_new_claims_export
for xml path('LineItem'), type
from #tbl_new_claims_export
for XML PATH('Line'),ROOT('Lines')
),'><','>'+char(10)+'<'),'<Num', char(9)+'<Num'), '<Num>', char(9)+'<Num>') ;

I am trying to create an output that looks like this:

<Line ID ="1">

<LineItem Name ="Michael"/>


<Num Number="24"</Num>




For each Line, I want to see the Line, Name, and Number as shown above. However, it is showing multiple Names under each Line and then repeats the Number below. Can anybody help me troubleshoot this code?



Without sample data with 1:n examples and the expected output it is reading in the magic glass bulb...

Anyway, this

    1 AS [Line/@ID]
   ,'Michael' AS [LineItem/@Name]
   ,24 AS [Numbers/Num/@Number]

will produce exactly the output you specify:

  <Line ID="1" />
  <LineItem Name="Michael" />
    <Num Number="24" />

If you need further help, please specify a minimal and reduced test scenario. Best would be a fiddle or some pasteable code like

DECLARE @tbl TABLE(ID INT, col1 VARCHAR(MAX)/*more columns*/);
INSERT INTO @tbl VALUES (1,'test1')/*more values*/