How do I fix my SQL statement to get the correct XML format?

Question

This is the first time I'm using 'For XML Path' in a SQL script. I'm close with the script but I just can't figure out how to get the XML to format correctly. Any idea what I'm doing wrong?

This is what I need.

<RecordsManager>
  <RecordSeries Id="41477">
    <RecordCategory Id="41477-104249">
        <Record Define="yes" Id="0001">
        <Record Define="yes" Id="0001" ResponsibleUser="BFRANKLIN"/>
        <Record Define="yes" Id="0002" ResponsibleUser="BFRANKLIN"/>
        </Record>
       </RecordCategory>
      </RecordSeries>
    </RecordsManager>

This is what I get. It's not putting in the END tag in each ResponsibleUser Record and is adding too many '</Record' tags.

  <RecordsManager>
   <RecordSeries Id="10070">
     <RecordCategory Id="10070-9452">
       <Record>
       <Record Define="yes" Id="0001">
       <Record Define="yes" Id="0001" ResponsibleUser="BFRANKLIN">
       <Record Define="yes" Id="0002" ResponsibleUser="BFRANKLIN"/>
       </Record>
      </Record>
     </Record>
    </Record>
   </Record>
  </RecordCategory>
 </RecordSeries>

This is the script I'm using.

 SELECT
     RecordSeriesId as [RecordSeries/@Id]
   , RecordCategoryId as [RecordSeries/RecordCategory/@Id]

   ,'yes' as [RecordSeries/RecordCategory/Record/@Define]
   ,'0001' as [RecordSeries/RecordCategory/Record/@Id]

  ,'yes'    as [RecordSeries/RecordCategory/Record/Record/@Define]
  ,'0001' as [RecordSeries/RecordCategory/Record/Record/@Id]
  , [ResponsibleUser] as [RecordSeries/RecordCategory/Record/Record/@ResponsibleUser]

  ,'yes' as [RecordSeries/RecordCategory/Record/Record/Record/@Define]
  ,'0002' as [RecordSeries/RecordCategory/Record/Record/Record/@Id]
  , [ResponsibleUser] as [RecordSeries/RecordCategory/Record/Record/Record/@ResponsibleUser]

 FROM Q_ChangeMatter
  WHERE RecordSeriesId = '10070'
 FOR xml path(''), ROOT('RecordsManager')

Answer 1

Prior to version 5.0, MySQL XML support was limited to exporting data in XML format using the MySQL command line client. Executing a command or query using the --xml or -X option told the MySQL client utility to produce the output as XML data. For instance, the following line would output all of the database variables that begin with the string “version”.

C:>mysql -u -p -e "SHOW VARIABLES LIKE '%version%'" –-xml

Answer 2

SELECT
     RecordSeriesId as [RecordSeries/@Id]
   , RecordCategoryId as [RecordSeries/RecordCategory/@Id]

   ,'yes' as [RecordSeries/RecordCategory/Record/@Define]
   ,'0001' as [RecordSeries/RecordCategory/Record/@Id]
   ,(
        select
          'yes'    as [Record/@Define]
          ,'0001' as [Record/@Id]
          , [ResponsibleUser] as [Record/@ResponsibleUser]
          ,'' 
          ,'yes' as [Record/@Define]
          ,'0002' as [Record/@Id]
          , [ResponsibleUser] as [Record/@ResponsibleUser]
        for xml path(''), type
     ) as [RecordSeries/RecordCategory/Record]
     
 FROM Q_ChangeMatter
  WHERE RecordSeriesId = '10070'
 FOR xml path(''), ROOT('RecordsManager');

Answer 3

Taking some inspiration from this question & answer.

Sample data

If your sample data does not look like this, then first write a query that gives you the necessary rows.

create table Q_ChangeMatter
(
  RecordSeriesId nvarchar(10),
  RecordCategoryId nvarchar(10),
  Id nvarchar(10),
  ResponsibleUser nvarchar(10)
);

insert into Q_ChangeMatter (RecordSeriesId, RecordCategoryId, Id, ResponsibleUser) values
('10070', '10070-8945', '0001', 'BFRANKLIN'),
('10070', '10070-8945', '0002', 'BFRANKLIN');

Solution

select RecordSeriesId as [@Id]
      ,(
       select RecordCategoryId as [@Id]
             ,'yes' as [@Define]
             ,(
              select '0001' as [@Id]
                    ,'yes' as [@Define]
                    ,(
                     select Id as [@Id]
                           ,ResponsibleUser as [@ResponsibleUser]
                     from Q_ChangeMatter T4
                     where T4.RecordSeriesId = T1.RecordSeriesId
                       and T4.RecordCategoryId = T2.RecordCategoryId
                     for xml path('Record'), type
                     )
              from Q_ChangeMatter T3
              where T3.RecordSeriesId = T1.RecordSeriesId
                and T3.RecordCategoryId = T2.RecordCategoryId
              group by RecordSeriesId, RecordCategoryId
              for xml path('Record'), type
              )
       from Q_ChangeMatter T2
       where T2.RecordSeriesId = T1.RecordSeriesId
       group by RecordCategoryId
       for xml path('RecordCategory'), type
       )
from Q_ChangeMatter T1
where RecordSeriesId = '10070'
group by RecordSeriesId
for xml path('RecordSeries'), root('RecordsManager');

Result

<RecordsManager><RecordSeries Id="10070"><RecordCategory Id="10070-8945" Define="yes"><Record Id="0001" Define="yes"><Record Id="0001" ResponsibleUser="BFRANKLIN"/><Record Id="0002" ResponsibleUser="BFRANKLIN"/></Record></RecordCategory></RecordSeries></RecordsManager>

Or with some more formatting:

<RecordsManager>
   <RecordSeries Id="10070">
      <RecordCategory Id="10070-8945" Define="yes">
         <Record Id="0001" Define="yes">
            <Record Id="0001" ResponsibleUser="BFRANKLIN" />
            <Record Id="0002" ResponsibleUser="BFRANKLIN" />
         </Record>
      </RecordCategory>
   </RecordSeries>
</RecordsManager>

Fiddle to see it in action.