| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Use RAW Mode with FOR XML |
Learn how using RAW mode with the FOR XML clause in a SQL query transforms the resulting XML data. |
MikeRayMSFT |
mikeray |
randolphwest |
05/05/2022 |
sql |
xml |
conceptual |
|
[!INCLUDE SQL Server Azure SQL Database]
RAW mode transforms each row in the query result set into an XML element that has the generic identifier <row>, or the optionally provided element name. By default, each column value in the rowset that is not NULL is mapped to an attribute of the <row> element. If the ELEMENTS directive is added to the FOR XML clause, each column value is mapped to a subelement of the <row> element. Together with the ELEMENTS directive, you can optionally specify the XSINIL option to map NULL column values in the result set to an element that has the attribute, xsi:nil="true".
You can request a schema for the resulting XML. Specifying the XMLDATA option returns an in-line XDR schema. Specifying the XMLSCHEMA option returns an in-line XSD schema. The schema appears at the start of the data. In the result, the schema namespace reference is repeated for every top-level element.
The BINARY BASE64 option must be specified in the FOR XML clause to return the binary data in base64-encoded format. In RAW mode, retrieving binary data without specifying the BINARY BASE64 option will result in an error.
This section contains the following examples:
- Example: Retrieving Product Model Information as XML
- Example: Specifying XSINIL with the ELEMENTS Directive
- Request schemas as results with XMLDATA & XMLSCHEMA
- Example: Retrieving Binary Data
- Example: Renaming the <row> Element
- Example: Specifying a Root Element for the XML Generated by FOR XML
- Example: Querying XMLType Columns