What is the purpose of FOR XML in SQL Server?
1. SQL Server 2000 provides the facility to retrieve data in the form of XML with the help of the FOR XML clause appended to the end of a SELECT statement.
2. There are 3 types of FOR XML modes —
RAW, AUTO, and EXPLICIT.3. They all generate a different style of XML document.
What is the OPENXML statement in SQL Server?
Going through thousands of lines of XML degrades performance to a great extent.This is where the OPENXML function comes into use. It does the parsing work for you quite efficiently.
The syntax of OPENXML is:OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)]
OPENXML function is supposed to be used with two system stored procedures:
1. sp_xml_preparedocument
2. sp_xml_removedocuments