Monday, March 12, 2012

For XML Path problems

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