SQL query FOR XML Path Root

Question

I want to display column called SampleDate from table SampleTable where column SampleAgencyName = something and below the date I want to display the value of a column called SampleNumber.

I mean the results should be something like this.

 <tr>
    <td> 
    Date1
    SampleNumber1
    </td>
    <td> 
    Date2
    SampleNumber2
    </td>
    </tr>

I want to do this using FOR XML Path Root in SQL SERVER.

I can get just the dates to work easily.

SELECT SampleDate 'td' from SampleTable T where SampleAgencyName = 'something' for xml path('tr'), root('table')

My issue how to get the value for the SampleNumber column under the date.

Which language are you using?

Daniel Casserly
– Daniel Casserly

2011-11-10 16:14:41 +00:00
Commented Nov 10, 2011 at 16:14 — Daniel Casserly
– Daniel Casserly, Commented Nov 10, 2011 at 16:14
@RedFilter ...I have posted what I have tried!

DotNetUser
– DotNetUser

2011-11-10 16:21:17 +00:00
Commented Nov 10, 2011 at 16:21 — DotNetUser
– DotNetUser, Commented Nov 10, 2011 at 16:21

Santy · Accepted Answer · 2011-11-10 17:01:03Z

1

Try this

SELECT SampleDate + '    ' + SampleNumber 'td' from SampleTable T where SampleAgencyName = 'something' for xml path('tr'), root('table')

Cast the colums as varchar in case any of them is numeric

In place of space you can use char(13) this will give the results in next line

SELECT SampleDate + char(13)
 + SampleNumber 'td' from SampleTable T where SampleAgencyName = 'something' for xml path('tr'), root('table')

edited Nov 10, 2011 at 17:01

answered Nov 10, 2011 at 16:41

Santy

851 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query FOR XML Path Root

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related