|
Jayeff_land
Starting Member
2 Posts |
 Posted - 2008-04-16 : 09:32:32
|
| Hi,Is it possible to format any kind of XML file with the FOR XML EXPLICIT mode in SQL Server 2005? I am asking because I am trying to format my file in a way that I could get 2 or 3 elements on my parent or child level and put some attributes (icon path for example) in these 2nd element or 3rd element. I am unable to format it that way. Let me show you the way that I would like to have my XML file formated :<?xml version="1.0" encoding="UTF-8" ?> <rows> <row id="14" sort="parent"> <cell icon="../../images/TreeGrid/folder.gif">Operations</cell> <row id="14.13" sort=”child”> <cell icon="../../images/TreeGrid/Red.gif">% SP</cell> <row id="14.13.1" sort=”sub-child”> <cell icon="../../images/TreeGrid/Red.gif">% SP Sub</cell> I want to have all my parent, children, sub-children to have these attribut but I am having difficulty putting one in the “Cell” tags. And I really think that my problem resides in the specifying of my column and their directives but I do not know how to fix it. See my icon path is commented here. I have to put the [row!2!cell!xmltext], but it is not working.Here is the way that I am building my Select query.SELECT 1 as tag, NULL as parent,NULL AS 'rows!1!', ----Root rows tagID_metric as [row!2!id],Description_english as [row!2!cell!Element],----Icon as [row!2!cell!xmltext],Sort as [row!2!sort], ID_metric as [row!3!id],Description_english as [row!3!cell!Element],--Icon as [row!3!cell!xmltext],sort as [row!3!sort], ID_metric as [row!4!id],Description_english as [row!4!cell!Element],--Icon as [row!4!cell!xmltext],sort as [row!4!sort], ID_metric as [row!5!id],Description_english as [row!5!cell!Element],--Icon as [row!5!cell!xmltext],sort as [row!5!sort], FROM #buildDataWHERE HLevel = 0UNION ALLSELECT 2 as tag, 1 as parent,NULL AS 'rows!1!',ID_metric as [row!2!id],Description_english as [row!2!cell!Element],--Icon as [row!2!cell!xmltext],sort as [row!2!sort], ID_metric as [row!3!id],Description_english as [row!3!cell!Element],--Icon as [row!3!cell!xmltext],sort as [row!3!sort], ID_metric as [row!4!id],Description_english as [row!4!cell!Element],--Icon as [row!4!cell!xmltext],sort as [row!4!sort], ID_metric as [row!5!id],Description_english as [row!5!cell!Element],--Icon as [row!5!cell!xmltext],sort as [row!5!sort],FROM #buildDataWHERE HLevel = 1UNION ALLSELECT 3 as tag, 2 as parent,NULL AS 'rows!1!',ID_metric as [row!2!id],Description_english as [row!2!cell!Element],--Icon as [row!2!cell!xmltext],sort as [row!2!sort], ID_metric as [row!3!id],Description_english as [row!3!cell!Element],--Icon as [row!3!cell!xmltext],sort as [row!3!sort],ID_metric as [row!4!id],Description_english as [row!4!cell!Element],--Icon as [row!4!cell!xmltext],sort as [row!4!sort], ID_metric as [row!5!id],Description_english as [row!5!cell!Element],--Icon as [row!5!cell!xmltext],sort as [row!5!sort],FROM #buildData WHERE HLevel = 2 UNION ALLSELECT 4 as tag, 3 as parent,NULL AS 'rows!1!',ID_metric as [row!2!id],Description_english as [row!2!cell!Element],--Icon as [row!2!cell!xmltext],sort as [row!2!sort],ID_metric as [row!3!id],Description_english as [row!3!cell!Element],--Icon as [row!3!cell!xmltext],sort as [row!3!sort],ID_metric as [row!4!id],Description_english as [row!4!cell!Element],--Icon as [row!4!cell!xmltext],sort as [row!4!sort], ID_metric as [row!5!id],Description_english as [row!5!cell!Element],--Icon as [row!5!cell!xml],sort as [row!5!sort],FROM #buildData WHERE HLevel = 3ORDER BY [row!2!sort], [row!3!sort], [row!4!sort], [row!5!sort]for XML explicitMy actual XML Result is this :<rows> <row id="14" sort="parent"> <cell>% SP</cell> <row id="14.13" sort="child"> <cell>% SP </cell> <row id="14.13.1" sort="child"> <cell>% SP cild </cell>Is it possible to customized anything with For XML Explicit?Anybody that can help my with that or indicate me where I am doing wrong? Thanks a lot. |
|
Unable to format my XML with For XML Explicit