Fix Error 6358 “…is not a valid style number when converting to XML” in SQL Server

If you’re getting error msg 6358 which reads something like 300 is not a valid style number when converting to XML, it’s probably because you’re trying to convert a value to XML, but the style that you’re specifying isn’t supported for conversions to that data type.

It’s not that the conversion can’t happen, it’s just that it can’t happen using the style that you’re specifying.

Continue reading

How to Add an Inline Schema When Using FOR XML in SQL Server

When using the FOR XML clause in a query in SQL Server, we can generate an XML document either with or without an inline schema.

To generate it with an inline XSD schema simply include the XMLSCHEMA argument. We can alternatively include the namespace if we want to specify a particular namespace.

At the time of writing, the XMLSCHEMA argument is only available when in either AUTO or RAW mode.

Continue reading

Fix Error Msg 6825 “ELEMENTS option is only allowed in RAW, AUTO, and PATH modes of FOR XML” in SQL Server

If you’re getting error msg 6825 that reads “ELEMENTS option is only allowed in RAW, AUTO, and PATH modes of FOR XML“, it’s probably because you’re trying to use the ELEMENTS directive while using EXPLICIT mode of the FOR XML clause.

As the message alludes to, this option is not available when using the EXPLICIT mode (it’s only allowed with the RAW, AUTO, and PATH modes).

However, SQL Server does provide us with an equivalent that provides the same result. When using EXPLICIT mode, we can use the ELEMENT directive instead. We apply this to the column names, instead of the end of the query. We can alternatively use the ELEMENTXSINIL directive to ensure that elements are generated even if the column contains a NULL value.

Continue reading

How to Include Elements that Contain NULL Values When Using FOR XML in SQL Server

When using FOR XML in SQL Server, we can use the ELEMENTS directive to include a column as an element instead of an attribute. However by default, if a column contains a NULL value, no element is produced for that column in the resulting XML document. This may or may not be what we want, depending on the requirements.

If we want such columns to be included in the XML even when they contain NULL values, all we need to do is include the XSINIL option. This option specifies that any column that has a NULL value automatically gets an element with xsi:nil="true" in the resulting XML.

The alternative is ABSENT, which means columns with NULL values are excluded (this is the default behaviour).

Continue reading

How to Include Elements that Contain NULL Values When Using FOR XML EXPLICIT in SQL Server

When using FOR XML EXPLICIT in SQL Server, we can use the ELEMENT directive to include a column as an element instead of an attribute. However, this directive doesn’t allow for NULL values. What I mean is that if a column contains a NULL value, no element is produced for that column in the resulting XML document. This may or may not be what we want, depending on the requirements.

If we want such columns to be included in the XML even when they contain NULL values, we can use the ELEMENTXSINIL directive instead of ELEMENT.

Continue reading

SQL Server FOR XML EXPLICIT Examples

In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document. Simply by placing the FOR XML clause at the end of the query will output the results in XML.

When we do this, we have the option of specifying RAW, AUTO, EXPLICIT, or PATH mode. These modes allow us to shape the resulting XML in different ways, and so the mode we choose will determine how the XML is generated.

Below are examples of using EXPLICIT mode when generating XML from a SQL query.

Continue reading

SQL Server FOR XML RAW Examples

In SQL Server, the FOR XML clause allows us to return the results of a query as an XML document. Simply by placing the FOR XML clause at the end of the query will output the results in XML.

When we do this, we have the option of specifying RAW, AUTO, EXPLICIT, or PATH mode. These modes allow us to shape the resulting XML in different ways, and so the mode we choose will determine how the XML is generated.

Below are examples of using RAW mode when generating XML from a SQL query.

Continue reading