February 22, 2011 at 10:18 pm
Hello All,
Please help me with this problem. I have a UI which has some combobox for selecting Database, its tables and its column. Whenever end user selects, I have to pass this data to my Stored Proc and retrun XML. Format for XML is like this:
<Data>
<Row>
<ColumnName Name="Column1">Value of Row1 Column1</ColumnName>
<ColumnName Name="Column2">Value of Row1 Column2</ColumnName>
<ColumnName Name="Column3">Value of Row1 Column3</ColumnName>
</Row>
<Row>
<ColumnName Name="Column1">Value of Row2 Column1</ColumnName>
<ColumnName Name="Column2">Value of Row2 Column2</ColumnName>
<ColumnName Name="Column3">Value of Row2 Column3</ColumnName>
</Row>
</Data>
How can I do this? I tried using FOR XML EXPLICIT but couldnt get the exact format.
Please help.
Thanks in advance.
February 22, 2011 at 11:19 pm
You really don't want to be using EXPLICIT, it tends to be way to onerous.
Try this:
declare @fun table(id int identity(1,1), val2 int, val3 int, val4 int)
insert @fun
values (1,2,3),(5,6,7)
select 'Column1' as 'ColumnName/@Name',
val2 as 'ColumnName',null as 'fn',
'Column2' as 'ColumnName/@Name',
val3 as 'ColumnName',null as 'fn',
'Column1' as 'ColumnName/@Name',
val4 as 'ColumnName'
from @fun
order by id
for XML path('Row'), Root('Data')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2011 at 1:30 am
Thank you so much... Its working fine now. Would you please explain this from the query:
'Column1' as 'ColumnName/@Name',
val2 as 'ColumnName',null as 'fn',
'Column2' as 'ColumnName/@Name'
Thanks again.
February 23, 2011 at 2:09 pm
most of those are baked into how the FOR XML expects to to give it names. The easiest thing there is to just read through the FOR XML documentation in BOL to see what is coming out. In short, addin the @ makes the value be expressed as an attribute in the XML otherwise it's an element.
The null is needed in order to express multiple instances of the same element one ofter the other.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply