覚え書きのようなものですが....。
SQL Server 2005では、FOR XMLの機能が、かなり良くなっていました。（私個人的には、『とっても』でした）
SQL Server 2000の時と同じ、FOR XML Auto とか RAWモードももちろん使えます。
ただし、上記のモードだと、お手軽ではあるんですが、Viewや一時テーブル、テーブル変数を使ったSELECTが、思いもよらないXMLで出力されることがありました。
でも、2005だと、PATHモードのおかげで、かなり自分の希望に近い形で、XMLを出力させることができるようになりました。
[今までのパターン]
SELECT Id AS Id, Name AS Name, ISNULL(NickName, Name) AS NickName
FROM [Test].[DocumentType] AS "DocumentType"
WHERE IsEnabled = 1
FOR XML Auto, Elements
-- 実行結果
<DocumentType>
<Id>1</Id>
<Name>見積り</Name>
<NickName>みつもり</NickName>
</DocumentType>
<DocumentType>
<Id>3</Id>
<Name>交通費精算</Name>
<NickName>こうつうひ</NickName>
</DocumentType>
上記は、FOR XML Autoモードの場合ですが、SELECT結果が複数あった場合は、1行につき1つのXMLで、複数のXMLが返ります。(e4x的に言うと、XMLListという感じでした。)
この場合、私が直面した困った点は、以下の通りです。
- ドキュメントルートのタグ（たとえば<root>...</root>）タグは自分でつけないといけませんでした。
- また、要素と属性を混在したXMLにするには、EXPLICITモードとかを使わないといけなかったのですが、これが非常にわかりづらいものでした。
- Viewを連結させた場合は、予想外の入れ子構造になっていたりしました。
なんとかあれこれ操作して、解決はできていたのですが、制限があって断念していたことも、いくつかありました。
[PATHモード利用]
さて、2005のドキュメントを見ると、PATHモードというものがあるのに、遅ればせながら気が付きました。
- PATHモードを利用すると、わりと理解しやすいSQLで、XMLの出力をコントロールできるようになります。
- EXPLICITモードというのが2000から使えたので、試してみたのですが、上記の通り、これが超理解に苦しみました。PATHモードを知った時、先にこっちを知っていれば...と、すくなからず後悔しました。
さて、SQLはこんな感じに指定しました。AS -> 要素名、@ -> 属性名になり、データは要素/属性のテキストノードとして格納されます。
SELECT (SELECT [Id] AS "@Id",
[Name] AS "@Name"
ISNULL([NickName], [Name]) AS "@NickName"
FROM [Test].[DocumentType]
WHERE IsEnabled = 1
FOR XML PATH('Item'), TYPE) AS "DocumentType"
FOR XML PATH(''), ROOT('root') -- root要素を追加
-- 実行結果
<root>
<DocumentType>
<Item Id="1" Name="見積り" NickName="みつもり" />
<Item Id="3" Name="交通費精算" NickName="こうつうひ" />
：
<Item Id="5" Name="顧客見積もり" NickName="こきゃくみつもり" />
</DocumentType>
</root>
今度は、root要素も指定できるし、要素と属性を混在させたりできます。
※ Namespace付きにする場合は、こんな感じで指定できました。
WITH XMLNAMESPACES (
'http://blogs.passj.org/akiko' as Akiko)
SELECT (SELECT [Id] AS "@Id"
,[Name] AS "@Name"
,ISNULL([NickName], [Name]) AS "@NickName"
FROM [Test].[DocumentType]
WHERE IsEnabled = 1
FOR XML PATH('Akiko:Item'), TYPE) AS "Akiko:DocumentType"
FOR XML PATH(''), ROOT('Akiko:root')
※WITH XMLNAMESPACES (....)がポイント。
結果はこんな感じになります。
<Akiko:root xmlns:Charon="http://blogs.passj.org/akiko">
<Akiko:DocumentType>
<Akiko:Item xmlns:Akiko="http://blogs.passj.org/akiko" Id="1" Name="見積り" NickName="みつもり" />
:
</Akiko:DocumentType>
</Akiko:root>