Microsoft SQL Server
FOR XML PATH

.NET Framework ASP.NET C# Language Entity Framework Java Language MySQL Oracle Database PHP SQL Visual Basic .NET Language
サーチ…


備考

他にもいくつかのFOR XMLモードがあります。

  • FOR XML RAW - <row>ごとに1つの<row>要素を作成します。
  • FOR XML AUTO - ヒューリスティックに階層をFOR XML AUTOしようとします。
  • FOR XML EXPLICIT - FOR XML EXPLICITの形状をより詳細に制御できますが、 FOR XML PATHよりも面倒です。

Hello World XML

SELECT 'Hello World' FOR XML PATH('example')

<example>Hello World</example>

名前空間の指定

SQL Server 2008 
WITH XMLNAMESPACES (
    DEFAULT 'http://www.w3.org/2000/svg',
    'http://www.w3.org/1999/xlink' AS xlink
)
SELECT 
    'example.jpg' AS 'image/@xlink:href',
    '50px' AS 'image/@width',
    '50px' AS 'image/@height'
FOR XML PATH('svg')

<svg xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/2000/svg">
    <image xlink:href="firefox.jpg" width="50px" height="50px"/>
</svg>

XPath式を使用した構造体の指定

SELECT
    'XPath example' AS 'head/title',
    'This example demonstrates ' AS 'body/p',
    'https://www.w3.org/TR/xpath/' AS 'body/p/a/@href',
    'XPath expressions' AS 'body/p/a'
FOR XML PATH('html')

<html>
    <head>
        <title>XPath example</title>
    </head>
    <body>
        <p>This example demonstrates <a href="https://www.w3.org/TR/xpath/">XPath expressions</a></p>
    </body>
</html>

FOR XML PATHでは、名前のない列はテキストノードになります。したがってNULLまたは''は空のテキストノードになります。注：名前付き列を名前のない列に変換するには、 AS * 

DECLARE @tempTable TABLE (Ref INT, Des NVARCHAR(100), Qty INT)
INSERT INTO @tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2)

SELECT ROW_NUMBER() OVER (ORDER BY Ref) AS '@NUM',
     'REF' AS 'FLD/@NAME', REF AS 'FLD', '',
     'DES' AS 'FLD/@NAME', DES AS 'FLD', '',
     'QTY' AS 'FLD/@NAME', QTY AS 'FLD'
FROM @tempTable 
FOR XML PATH('LIN'), ROOT('row')

<row>
  <LIN NUM="1">
    <FLD NAME="REF">100001</FLD>
    <FLD NAME="DES">Normal</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
  <LIN NUM="2">
    <FLD NAME="REF">100002</FLD>
    <FLD NAME="DES">Foobar</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
  <LIN NUM="3">
    <FLD NAME="REF">100003</FLD>
    <FLD NAME="DES">Hello World</FLD>
    <FLD NAME="QTY">2</FLD>
  </LIN>
</row>

（空の）テキストノードを使用すると、以前に出力されたノードを次のノードから切り離すことができ、SQL Serverは次の列の新しい要素を開始することができます。それ以外の場合は、属性が「現在の」要素と思われるものに既に存在する場合は混乱します。

たとえば、 SELECTステートメントの要素と属性の間に空の文字列がないと、SQL Serverにエラーが発生します。

属性中心の列 'FLD / @ NAME'は、FOR XML PATHのXML階層内の属性中心でない兄弟の後に来てはなりません。

また、この例では、 ROOT('row')で指定されたrowという名前のルート要素にXMLをラップしています

FOR XML PATHを使用した値の連結

FOR XML PATHは、値を文字列に連結するために使用できます。次の例では、値をCSV文字列に連結しています。 

DECLARE @DataSource TABLE
(
    [rowID] TINYINT
   ,[FirstName] NVARCHAR(32)
);

INSERT INTO @DataSource ([rowID], [FirstName])
VALUES (1, 'Alex')
      ,(2, 'Peter')
      ,(3, 'Alexsandyr')
      ,(4, 'George');

SELECT STUFF
(
    (
        SELECT ',' + [FirstName]
        FROM @DataSource
        ORDER BY [rowID] DESC
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

いくつかの重要なメモ：

  • ORDER BY句を使用して、値を優先的に並べ替えることができます
  • 連結セパレータとして長い値を使用する場合は、 STUFF関数のパラメータも変更する必要があります。 
SELECT STUFF
(
    (
        SELECT '---' + [FirstName]
        FROM @DataSource
        ORDER BY [rowID] DESC
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,3 -- the "3" could also be represented as: LEN('---') for clarity
    ,''
);
  • TYPEオプションと.value関数が使用されるので、連結はNVARCHAR(MAX)文字列で機能します


Modified text is an extract of the original Stack Overflow Documentation
ライセンスを受けた CC BY-SA 3.0
所属していない Stack Overflow