Can anyone tell me how to add multiple columns with the same name? Here is an example of the XML format I'm trying to create using For XML Path
<TABLETYPE TYPEABBRV=“IDEADISAB” TOTALINDICATOR=“N”>
<CATEGORY TYPE=“DISABCATIDEA” VALUE=“AUT”/>
<CATEGORY TYPE=“AGESA” VALUE=“6”/>
<CATEGORY TYPE=“EDENVIRIDEASA” VALUE=“RC80”/>
<AMOUNT>10</AMOUNT>
</TABLETYPE>
Here is the query I was trying to use
SELECT
'IDEADISAB' AS '@.TYPEABBRV',
'N' AS '@.TOTALINDICATOR',
'DISABCATIDEA' AS 'CATEGORY/@.TYPE',
IdeaCategory AS 'CATEGORY/@.VALUE',
'AGESA' AS 'CATEGORY/@.TYPE',
AGE AS 'CATEGORY/@.VALUE',
'EDENVIRIDESAS' AS 'CATEGORY/@.TYPE',
EECATEGORY AS 'CATEGORY/@.VALUE',
COUNT(*) AS 'AMOUNT'
FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory
FOR XML PATH('TABLETYPE'), TYPE)
And this is the error I'm getting
Msg 6810, Level 16, State 1, Line 1
Column name 'CATEGORY/@.TYPE' is repeated. The same attribute cannot be generated more than once on the same XML tag.
Any help would be much appreciated
Use subqueries
SELECT
'IDEADISAB' AS '@.TYPEABBRV',
'N' AS '@.TOTALINDICATOR',
(SELECT
'DISABCATIDEA' AS '@.TYPE',
IdeaCategory AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'AGESA' AS '@.TYPE',
AGE AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
(SELECT
'EDENVIRIDESAS' AS '@.TYPE',
EECATEGORY AS '@.VALUE'
FOR XML PATH('CATEGORY'),TYPE),
COUNT(*) AS 'AMOUNT'
FROM EdenIdeaStudents group by Age, EeCategory, IdeaCategory
FOR XML PATH('TABLETYPE'), TYPE
Thanks Mark,
I had already tried using subqueries but could not quite get the syntax correct. I was using the 'from' statement after every select and that was throwing all of my records under one tag. Thanks again.
No comments:
Post a Comment