<row> 元素,欄位為屬性 (預設) 或子元素。WITH TestXml AS (
SELECT 1 AS id, N'LeeWhoeeUniversity' AS name
UNION ALL SELECT 2, N'SQL Server XML'
UNION ALL SELECT 3, NULL
)
SELECT id, name FROM TestXml FOR XML RAW, TYPE;
結果:
<row id="1" name="LeeWhoeeUniversity" />
<row id="2" name="SQL Server XML" />
<row id="3" />
TYPE 指示回傳 xml 型別而非 nvarchar(max),不影響結構。
SELECT id, name FROM TestXml FOR XML RAW, ELEMENTS;
<row>
<id>1</id>
<name>LeeWhoeeUniversity</name>
</row>
<row>
<id>2</id>
<name>SQL Server XML</name>
</row>
<row>
<id>3</id>
<!-- name 缺失,因 NULL 預設不產生元素 -->
</row>
SELECT id, name FROM TestXml FOR XML RAW, ELEMENTS XSINIL;
<row xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">
<id>1</id><name>LeeWhoeeUniversity</name>
</row>
<row xmlns:xsi="...">
<id>3</id><name xsi:nil="true" />
</row>
-- XMLDATA (舊式 Microsoft XDR)
SELECT id, name FROM TestXml FOR XML RAW, XMLDATA;
-- XMLSCHEMA (標準 XSD) - 可指定目標命名空間
SELECT id, name FROM TestXml FOR XML RAW, XMLSCHEMA('urn:https://example.com/ns');
輸出包含 <Schema> 或 <xsd:schema> 區塊,後接資料列。
SELECT Id, Photo FROM Employees FOR XML RAW, BINARY BASE64;
將 varbinary/image 欄位以 Base64 編碼輸出。
SELECT id, name FROM TestXml FOR XML RAW('MyRow'), ELEMENTS;
<MyRow><id>1</id><name>...</name></MyRow>...
SELECT id, name FROM TestXml FOR XML RAW, ROOT('MyData');
<MyData>
<row id="1" name="..." />...
</MyData>
FOR XML PATH, ROOT('Response'), ELEMENTS XSINIL, TYPE 產生標準化 XML。FOR XML AUTO, TYPE 直接輸出至 XmlReader,避免記憶體爆炸。WITH XMLNAMESPACES 宣告前綴,配合 FOR XML PATH 精確控制。