What is for xml?
If we use SELECT query
it returns as a rowset but there it is optional to retrieve formal results of
SQL query as XML by specifying FOR XML. FOR XML is used in top level queries. In
a FOR XML clause, you specify one of these modes:
You can include a FOR XML clause only in SELECT statements,
if those statements define the outer, or top-level, query. However, you can
also include the clause in INSERT, UPDATE, and DELETE statements that are part
of a sub-query.
How to use for xml?
[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML
{
{
RAW [ ('ElementName') ] | AUTO }
[
<CommonDirectives>
[ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ]
[ , ELEMENTS [ XSINIL | ABSENT ]
]
|
EXPLICIT
[
<CommonDirectives>
[ , XMLDATA ]
]
|
PATH [ ('ElementName') ]
[
<CommonDirectives>
[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
<CommonDirectives> ::=
[ ,
BINARY BASE64 ]
[ ,
TYPE ]
[ ,
ROOT [ ('RootName') ] ]
|
RAW Mode
The RAW mode generates a single XML element for each row in the result set returned by the
SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON c.ContactID
= e.ContactID
WHERE c.FirstName = 'XYZ'
FOR XML RAW;
|
Output:
<row EmployeeID="123" FirstName="XYZ" LastName="XYZ" />
<row EmployeeID="125" FirstName="XYZ" MiddleName="AA" LastName="ABC" />
|
Now in output we can see there “row” we can rename it by
adding ('Employee') after RAW in the query
The AUTO Mode
The AUTO mode in a FOR XML clause is slightly different from the RAW mode in the way that it generates the XML result set. The AUTOmode generates the XML by using heuristics based on how the SELECT statement is defined. The best way to understand how this works is to look at an example. The following SELECT statement, as in the previous examples
SELECT Employee.EmployeeID, ContactInfo.FirstName,
ContactInfo.MiddleName, ContactInfo.LastName
FROM HumanResources.Employee AS Employee
INNER JOIN Person.Contact AS ContactInfo
ON ContactInfo.ContactID = Employee.ContactID
WHERE ContactInfo.FirstName = 'XYZ'
FOR XML AUTO, ROOT ('Employees');
|
Output:
</Employees>
<Employees>
<Employee EmployeeID="123">
<ContactInfo FirstName="XYZ" LastName="XYZ" />
</Employee>
<Employee EmployeeID="125">
<ContactInfo FirstName="XYZ" MiddleName="AA" LastName="ABC" />
</Employee>
</Employees>
|
The EXPLICIT Mode
The EXPLICIT mode provides very specific control over your XML, but this mode is much more complex to use than the RAW or AUTOmodes. To use this mode, you must build your SELECT statements in such as way as to define the XML hierarchy and structure. In addition, you must create a SELECT statement for each level of that hierarchy and use UNION ALL clauses to join those statements.
SELECT 1 AS Tag,
NULL AS Parent,
e.EmployeeID AS [Employee!1!EmployeeID],
NULL AS [ContactInfo!2!FirstName!ELEMENT],
NULL AS [ContactInfo!2!MiddleName!ELEMENT],
NULL AS [ContactInfo!2!LastName!ELEMENT]
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
WHERE c.FirstName = 'XYZ'
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
e.EmployeeID,
c.FirstName,
c.MiddleName,
c.LastName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE c.FirstName = 'XYZ'
ORDER BY [Employee!1!EmployeeID], [ContactInfo!2!FirstName!ELEMENT]
FOR XML EXPLICIT;
|
Output:
<Employee EmployeeID="123">
<ContactInfo>
<FirstName>XYZ</FirstName>
<LastName>XYZ</LastName>
</ContactInfo>
</Employee>
<Employee EmployeeID="125">
<ContactInfo>
<FirstName>XYZ</FirstName>
<MiddleName>AA</MiddleName>
<LastName>ABC</LastName>
</ContactInfo>
</Employee>
|
The PATH Mode
When you specify the PATH mode in the FOR XML clause, column names (or their aliases) are treated as XPath expressions that determine how the data values will be mapped to the XML result set. By default, XML elements are defined based on column names. You can modify the default behavior by using the at (@) symbol to define attributes or the forward slash (/) to define the hierarchy. Let’s take a look at a few examples to demonstrate how all this works.
SELECT e.EmployeeID, c.FirstName,
c.MiddleName, c.LastName
FROM HumanResources.Employee AS e
INNER JOIN Person.Contact AS c
ON c.ContactID = e.ContactID
WHERE c.FirstName = 'XYZ'
FOR XML PATH ('Employee'), ROOT ('Employees');
|
<Employees>
<Employee>
<EmployeeID>123</EmployeeID>
<FirstName>XYZ</FirstName>
<LastName>XYZ</LastName>
</Employee>
<Employee>
<EmployeeID>125</EmployeeID>
<FirstName>XYZ</FirstName>
<MiddleName>AA</MiddleName>
<LastName>ABC</LastName>
</Employee>
</Employees>
|
