CODEWITHSUNDEEP
sql-serverxml
ExtremeSwat
ExtremeSwat

Reputation: 824

SQL Server - Getting XML from result set within one tag

I am trying without much success to build a XML out of my result set. My result set should look like this : 

   <CSVRoot>
        <CsvColumn>MDCY_Code</CsvColumn>
        <CsvColumn>MDCY_Description</CsvColumn>
    </CSVRoot>

This is what my query returns : 

<CSVRoot>
  <CSVHeader>
    <CsvColumn>MDCY_Code</CsvColumn>
  </CSVHeader>
  <CSVHeader>
    <CsvColumn>MDCY_Description</CsvColumn>
  </CSVHeader>
</CSVRoot>

I know that I can use flowr on this formed XML and remake the XML, but that's additional processing. Is there any way to do this without further processing ( such as using a flowr to form a new XML ) in one query?

This is what I've used:

select utma.CsvColumn as [ColumnName] from Methods m inner join UITestMap utm on m.UITestMapID = utm.ID inner join SubMethods sm on m.Id = sm.HeaderID inner join UITestMapActions utma on sm.UITestMapActionID = utma.ID

for xml path('CSVHeader'),root('CSVRoot')

Edit 2 :

This isn't working, I get the same output.Utma has one column that I need in my query and that is CSVColumn.

Edit 3:

Now I'm getting this : 

<CsvHeader>
  <ColumnName>MDCY_Cod</ColumnName>
  <ColumnName>MDCY_Descriere</ColumnName>
</CsvHeader>

Which is the correct answer. I wasn't sure how I wanted to process the result sets, actually I will need to use flowr to create a new xml out of this output. ( which is what I needed in the first place)

Upvotes: 2

Views: 90

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Try this:

select
    utma.CsvColumn
from
    Methods m
for xml path(''),root('CSVRoot')

Upvotes: 1

Related Questions