Retrieving XML Data from SQL server

1
Retrieving XML Data from SQL server
2
Using the FOR XML Clause to Retrieve Data

• Retrieving Data in XML Format
• How SQL Server Generates XML
• Using the FOR XML Clause
• Using RAW Mode to Retrieve XML
• Using AUTO Mode to Retrieve XML
• Demo Using RAW and AUTO

3
Retrieving Data in XML Format
Retailer
4
How SQL Server Generates XML
SELECT FROM WHERE FOR XML MODE
5
Using the FOR XML Clause

• The FOR XML Clause Syntax
• XML Document Fragments

SELECT select_list FROM table_source WHERE
search_condition FOR XML RAW AUTO EXPLICIT
, XMLDATA , ELEMENTS , BINARY BASE64
ltOrder OrderID"10248" OrderDate"07/04/1996"/gt ltO
rder OrderID"10249" OrderDate"07/05/1996"/gt
6
Using RAW Mode to Retrieve XML

• Generic ltrowgt element produced for each row
  returned by the query

SELECT OrderID, OrderDate FROM Orders FOR XML RAW
ltrow OrderID"10248" OrderDate"07/04/1996"/gt ltrow
OrderID"10249" OrderDate"07/05/1996"/gt
7
Using AUTO Mode to Retrieve XML

• Table name(s) are used to name elements

SELECT OrderID, OrderDate FROM Orders FOR XML AUTO
ltOrders OrderID"10248" OrderDate"07/04/1996"/gt lt
Orders OrderID"10249" OrderDate"07/05/1996"/gt
8
Demonstration Using RAW and AUTO
9
Controlling XML Output

• Specifying the ELEMENTS Option
• Working with Multiple Tables
• Demo Retrieving Data from Multiple Tables
• Specifying the XMLDATA Option
• Retrieving Binary Data

10
Specifying the ELEMENTS Option

• Columns are returned as child elements

SELECT OrderID, OrderDate FROM Orders FOR XML
AUTO, ELEMENTS
ltOrdersgt ltOrderIDgt10248lt/OrderIDgt
ltOrderDategt07/04/1996lt/OrderDategt lt/Ordersgt ltOrder
sgt ltOrderIDgt10249lt/OrderIDgt
ltOrderDategt07/05/1996lt/OrderDategt lt/Ordersgt
11
Working with Multiple Tables
SELECT OrderForm.OrderID, Item.ProductID
Item.Quantity FROM Orders OrderForm JOIN Order
Details Item ON OrderForm.OrderID
Item.OrderID ORDER BY OrderForm.OrderID FOR XML
RAW
ltrow OrderID"10248" ProductID"1"
Quantity"12"/gt ltrow OrderID"10248"
ProductID"42" Quantity"10"/gt
SELECT OrderForm.OrderID, Item.ProductID
Item.Quantity FROM Orders OrderForm JOIN Order
Details Item ON OrderForm.OrderID
Item.OrderID ORDER BY OrderForm.OrderID FOR XML
AUTO
ltOrderForm OrderID"10248"gt ltItem ProductID"1"
Quantity"12"/gt ltItem ProductID"42"
Quantity"10"/gt lt/OrderFormgt
12
Demonstration Retrieving Data from Multiple
Tables
13
Specifying the XMLDATA Option

• XML-Data Reduced (XDR) Schema pre-pended to
  results

SELECT OrderID, OrderDate FROM Orders FOR XML
AUTO, XMLDATA
ltSchema name"Schema1" xmlns"urnschemas-micr
osoft-comxml-data" xmlnsdt"urnschemas-micro
soft-comdatatypes"gt ltElementType name"Orders"
content"empty model"closed"gt ltAttributeTy
pe name"OrderID" dttype"14"/gt ltAttributeType
name"OrderDate" dttype"dateTime"/gt ltattribute
type"OrderID"/gt ltattribute type"OrderDate"/gt lt/E
lementTypegt lt/Schemagt
14
Retrieving Binary Data
SELECT EmployeeID, Photo FROM Employees WHERE
EmployeeID 10 FOR XML AUTO
ltEmployees EmployeeID"1" Photo"dbObject/Employe
es_at_EmpID'1'/_at_Photo"/gt
SELECT EmployeeID, Photo FROM Employees WHERE
EmployeeID 1 FOR XML AUTO, BINARY BASE64
ltEmployees EmpID"1" Photo"FRwvAAIAAAANAA4AFAAhA
P////9Ca ..."/gt
15
Generating Custom XML Formats

• Custom XML Formats
• Universal Tables
• Using EXPLICIT Mode
• Demonstration Universal Tables and XML
• Using EXPLICIT Mode with Multiple Tables

16
Custom XML Formats

• Columns in a table that are mapped to an XML
  element can be represented as
• Element values
• Attributes
• Child elements

ltInvoice InvoiceNo"10248"gt ltDategt1996-07-04T00
0000lt/Dategt ltLineItem ProductID"11"gtQueso
Cabraleslt/LineItemgt ltLineItem
ProductID"42"gtSingaporean Fried
Meelt/LineItemgt lt/Invoicegt
17
Universal Tables

• Tabular representation of an XML document
• Tag and Parent columns determine hierarchy
• Column names determine element / attribute mapping

Tag Parent Invoice!1!InvoiceNo Invoice!1!Date!Element LineItem!2!ProductID LineItem!2
1 NULL 10248 1996-07-04T000000 NULL NULL
2 1 10248 NULL 11 Queso Cabrales
2 1 10248 NULL 42 Singaporean
18
Using EXPLICIT Mode

• Construct the Transact-SQL to generate the
  universal table
• Add the FOR XML EXPLICIT clause

SELECT 1 AS Tag, NULL AS Parent, OrderID
AS Invoice!1!InvoiceNo, OrderDate AS
Invoice!1!Date!Element FROM Orders WHERE
OrderID 10248 FOR XML EXPLICIT
ltInvoice InvoiceNo"10248"gt ltDategt1996-07-04T00
0000lt/Dategt lt/Invoicegt
19
Using EXPLICIT Mode with Multiple Tables

• Use the UNION ALL operator to retrieve multiple
  Tag values

SELECT 1 AS Tag, NULL AS Parent, OrderID
AS Invoice!1!InvoiceNo, OrderDate AS
Invoice!1!Date!Element, NULL AS
LineItem!2!ProductID, NULL AS
LineItem!2 FROM Orders WHERE OrderID10248 UNION
ALL SELECT 2 AS Tag,1 AS Parent, OD.OrderID, NU
LL, OD.ProductID, P.ProductName FROM Order
Details OD JOIN Orders O ON OD.OrderIDO.OrderID
JOIN Products P ON OD.ProductID
P.ProductID WHERE OD.OrderID10248 ORDER BY
Invoice!1!InvoiceNo, LineItem!2!ProductID FOR
XML EXPLICIT
20
Demonstration Universal Tables and XML
21
Best Practices

• Use RAW Mode for Aggregated Data
• Use AUTO Mode for Attribute-Centric or
  Element-Centric XML
• Use Aliases with AUTO and RAW to Name XML
  Elements and Attributes
• Use an ORDER BY Clause when Querying Multiple
  Tables
• Use EXPLICIT Mode for XML Documents Containing a
  Mix of Attribute-Centric and Element-Centric
  Mappings