Help to fix FOR XML EXPLICIT qry

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Help to fix FOR XML EXPLICIT qry

Author

Topic

dankad77
Starting Member

2 Posts

Posted - 2011-01-21 : 14:17:54

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

************************

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-21 : 14:22:19

Please do not cross post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155567

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources