PdfsLibrary focus on small piece of information that stored in pdf files,
this information for specific fields, specific subject or solve specific
problems. Come and join with us to download all your need or upload
what you have.
[Link]
About main source
Course Ref. 2779
Using XML with SQL Server 2005
Using XML with SQL Server 2005
Contents
Using XML ............................................................................................................................................................................. 3
Retrieving Data in Generic Row Elements .......................................................................................................................... 3
FOR XML RAW................................................................................................................................................................. 3
Retrieving Data as Elements............................................................................................................................................... 3
Retrieving Data by Using a Root Element and a Customized Row Element Name ............................................................... 4
Retrieving Nested Data by Using AUTO Mode .................................................................................................................... 4
Retrieving Data as Elements............................................................................................................................................... 4
EXPLICIT Mode Queries - Defining Column Mappings in a Universal Table.......................................................................... 5
Retrieving Data by Using PATH Mode................................................................................................................................. 7
Retrieving Data by Using PATH Mode - Modifying the Row Element Name ........................................................................ 8
Nested XML - Using TYPE to Return the xml Data Type in a Subquery ................................................................................ 8
[Link]
Page 2 of 9
Using XML with SQL Server 2005
Using XML
Retrieving Data in Generic Row Elements
The following example shows how you can retrieve an XML fragment containing order data by using a FOR XML query in
RAW mode.
SELECT [Link] CustID, CustomerType, SalesOrderID
FROM [Link] Cust JOIN [Link] [Order]
ON [Link] = [Order].CustomerID
ORDER BY [Link]
FOR XML RAW
Retrieving Data as Elements
The following example shows how you can retrieve the same data as elements instead of attributes by specifying the
ELEMENTS option.
SELECT [Link] CustID, CustomerType, SalesOrderID
FROM [Link] Cust JOIN [Link] [Order]
ON [Link] = [Order].CustomerID
ORDER BY [Link]
FOR XML RAW, ELEMENTS
[Link]
Page 3 of 9
Using XML with SQL Server 2005
Retrieving Data by Using a Root Element and a Customized Row Element Name
The following example shows how you can retrieve the same data by using a root element specified with the ROOT option
and modify the row element name by using the RAW mode optional argument.
SELECT [Link] CustID, CustomerType, SalesOrderID
FROM [Link] Cust JOIN [Link] [Order]
ON [Link] = [Order].CustomerID
ORDER BY [Link]
FOR XML RAW('Order'), ROOT('Orders')
Retrieving Nested Data by Using AUTO Mode
The following example shows how you can use an AUTO mode query to return an XML fragment containing a list of orders.
SELECT [Link] CustID, CustomerType, SalesOrderID
FROM [Link] Cust JOIN [Link] [Order]
ON [Link] = [Order].CustomerID
ORDER BY [Link]
FOR XML AUTO
Retrieving Data as Elements
The following example shows how you can retrieve the same data as elements instead of attributes by specifying the
ELEMENTS option.
SELECT [Link] CustID, CustomerType, SalesOrderID
FROM [Link] Cust JOIN [Link] [Order]
ON [Link] = [Order].CustomerID
ORDER BY [Link]
FOR XML AUTO, ELEMENTS
[Link]
Page 4 of 9
Using XML with SQL Server 2005
As for RAW mode, you can also use the ROOT option, as shown in the following example.
SELECT [Link] CustID, CustomerType, SalesOrderID
FROM [Link] Cust JOIN [Link] [Order]
ON [Link] = [Order].CustomerID
ORDER BY [Link]
FOR XML AUTO, ELEMENTS, ROOT('Orders')
EXPLICIT Mode Queries - Defining Column Mappings in a Universal Table
SELECT 1 AS Tag,
NULL AS Parent,
SalesOrderID AS [Invoice!1!InvoiceNo],
OrderDate AS [Invoice!1!Date!Element]
FROM [Link]
FOR XML EXPLICIT
[Link]
Page 5 of 9
Using XML with SQL Server 2005
[Link]
Page 6 of 9
Using XML with SQL Server 2005
Retrieving Data by Using PATH Mode
The following example shows how you can use a PATH mode query to return an XML fragment containing a list of
employees
SELECT EmployeeID "@EmpID",
FirstName "EmpName/First",
LastName "EmpName/Last"
FROM [Link] INNER JOIN
[Link] ON [Link] = [Link]
FOR XML PATH
[Link]
Page 7 of 9
Using XML with SQL Server 2005
Retrieving Data by Using PATH Mode - Modifying the Row Element Name
The following example shows how you can use the optional ElementName argument to the PATH mode query to modify the
default row element name.
SELECT EmployeeID "@EmpID",
FirstName "EmpName/First",
LastName "EmpName/Last"
FROM [Link] INNER JOIN
[Link] ON [Link] = [Link]
FOR XML PATH('Employee')
Nested XML - Using TYPE to Return the xml Data Type in a Subquery
SQL Server 2005 includes the xml data type. Specifying the TYPE directive in a FOR XML query returns the results as an xml
value instead of as a varchar string. The most significant impact of this is the ability to nest FOR XML queries to return
multilevel XML results in AUTO and RAW mode queries. The following example shows how to use the TYPE directive to nest
FOR XML queries.
SELECT Name CategoryName,
(SELECT Name SubCategoryName
FROM [Link] SubCategory
WHERE [Link]=[Link]
FOR XML AUTO, TYPE, ELEMENTS)
FROM [Link] Category
FOR XML AUTO
[Link]
Page 8 of 9
Using XML with SQL Server 2005
Nesting Tables by Using EXPLICIT Mode
SELECT 1 AS Tag,
NULL AS Parent,
SalesOrderID AS [Invoice!1!InvoiceNo],
OrderDate AS [Invoice!1!Date!Element],
NULL AS [LineItem!2!ProductID],
NULL AS [LineItem!2]
FROM [Link]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
[Link],
NULL,
[Link],
[Link]
FROM [Link] OrderDetail JOIN
[Link] OrderHeader
ON [Link]= [Link]
JOIN [Link] Product
ON [Link] = [Link]
ORDER BY [Invoice!1!InvoiceNo], [LineItem!2!ProductID]
FOR XML EXPLICIT
[Link]
Page 9 of 9
