0

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')
user3119773
  • 73
  • 9
  • 4
    It would be much easier to follow if you would post your expected and actual XMLs as text (inside the ``` code tag) rather than as pictures. Re-typing it manually is extremely prone to errors, and reading it with eyes, the way it is formatted, doesn't help much either. – Roger Wolf Dec 06 '20 at 02:27
  • Thanks for the tip. I mistakenly thought adding images would be easier to read. – user3119773 Dec 06 '20 at 20:23

3 Answers3

1

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.

Sander
  • 3,942
  • 2
  • 17
  • 22
1
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');
lptr
  • 1
  • 2
  • 6
  • 16
0

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

Ronak
  • 1