Multiple Nodes Sql -> XML

I'm trying to add multiple nodes with the same fields in them and SQL isn't too happy about that.

What I want is
<ADM>
   <Coverage_Loss>
      <Coverage id="COV1" />
      <Coverage id="COV2"/>
      <Coverage id="COV3"/>
   </Coverage_Loss>
</ADM>
etc.

I have this (I omitted some irrelevant fields):
Select 'COV1' as 'ADM/COVERAGE_LOSS/COVERAGES/COVERAGE/@id'
         ,'COV2' as 'ADM/COVERAGE_LOSS/COVERAGES/COVERAGE/@id'
         ,'COV3' as 'ADM/COVERAGE_LOSS/COVERAGES/COVERAGE/@id'
FROM tablesblah
FOR XML PATH ('XMLPATHHERE')

Open in new window


But I get the error:
Column name 'ADM/COVERAGE_LOSS/COVERAGES/COVERAGE/@id' is repeated. The same attribute cannot be generated more than once on the same XML tag.

Is there a way to get the multiple nodes with the same names working here?
Josh_BladeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
wdosanjosConnect With a Mentor Commented:
You need to include a sub-query to return COVERAGE as a set of rows.  Something like below.  But I'm not sure how that fits into your query as you would need to have your column values as rows (you could use a pivot query for that).
Select 
    (Select COVERAGE as 'COVERAGE/@id'
    From
    (
        select 'COV1' COVERAGE
        union
        select 'COV2' COVERAGE
        union
        select 'COV3' COVERAGE
    ) a For XML PATH(''), type) as 'ADM/COVERAGE_LOSS/COVERAGES'
--FROM tablesblah
FOR XML PATH ('')

/*
Output:

<ADM>
  <COVERAGE_LOSS>
    <COVERAGES>
      <COVERAGE id="COV1" />
      <COVERAGE id="COV2" />
      <COVERAGE id="COV3" />
    </COVERAGES>
  </COVERAGE_LOSS>
</ADM>

*/

Open in new window

0
 
Josh_BladeAuthor Commented:
Thanks for the help. That looks like it will do it. I just need to hammer out the details now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.