Reputation: 83
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')
Upvotes: 0
Views: 90
Reputation: 6808
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');
Upvotes: 1
Reputation: 4042
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.
Upvotes: 1
Reputation: 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
Upvotes: 0