dankad77
Starting Member
2 Posts |
Posted - 2011-01-21 : 13:57:29
|
Hi I have a table with 5 columns and need to fix my For XML Explicit qry. Anyone can help to find the issue? Table: ParameterId ParameterName Abbreviation AttributePropertyName AttributePropertyValue 1274 History, family: description Family History Comments Summary of changes 1/6/2011, automated process, new entry 1274 History, family: description Family History Comments Alternate name Family History Comments 1274 History, family: description Family History Comments Alternate category History and Physical/ Consultation 1274 History, family: description Family History Comments Status Approved
<Parameter> <ParameterId>1274</ParameterId> <ParameterName>History, family: description</ParameterName> <Abbreviation>Family History Comments</Abbreviation> <Attributes> <Attribute> <AttributePropertyName>Summary of changes</AttributePropertyName> <AttributePropertyValue>1/6/2011, automated process, new entry</AttributePropertyValue> </Attribute> </Attributes> </Parameter>
Instead I'm getting extra <Attributes /> If there are 2 Attributes then I get 2 extra <Attributes /> As: <Parameter> <ParameterId>1274</ParameterId> <ParameterName>History, family: description</ParameterName> <Abbreviation>Family History Comments</Abbreviation> <Attributes /> <Attributes> <Attribute> <AttributePropertyName>Summary of changes</AttributePropertyName> <AttributePropertyValue>1/6/2011, automated process, new entry</AttributePropertyValue> </Attribute> </Attributes> </Parameter>
My qry: select 1 As Tag , NULL As Parent, 0 AS [Parameters!1!Sort!hide], NULL AS [Parameters!1!], -- root element NULL AS [Parameter!2!ParameterId!element], NULL AS [Parameter!2!ParameterName!element], NULL AS [Parameter!2!Abbreviation!element], NULL AS [Attributes!3!element], -- root element for Attributes NULL AS [Attribute!4!AttributePropertyName!element], NULL AS [Attribute!4!AttributePropertyValue!element]
UNION SELECT 2 AS Tag, 1 AS Parent, ParameterId * 100, NULL AS [Parameters!1!], -- root element ParameterId,-- AS [Parameter!1!ParameterId!element], ParameterName, Abbreviation, NULL AS [Attributes!3!element], -- root element for Attributes NULL, NULL
from @tempAttributes
UNION ALL SELECT 3 AS Tag, 2 AS Parent, ParameterId * 100 + 1, NULL AS [Parameters!1!], -- root element, -- root element for Parameter NULL, NULL, NULL, NULL AS [Attributes!3!element], -- root element for Attributes NULL, --AttributePropertyName, NULL --AttributePropertyValue
from @tempAttributes
UNION ALL SELECT 4 AS Tag, 3 AS Parent, ParameterId * 100 + 2, NULL AS [Parameters!1!], -- root element Null, NULL, NULL, NULL AS [Attributes!3!element], -- root element for Attributes AttributePropertyName, AttributePropertyValue
from @tempAttributes
Order by [Parameters!1!Sort!hide]--, [Parameter!2!ParameterId!element] FOR XML EXPLICIT
************************ |
|