Retrieving XML Data from SQL server.  Using the FOR XML Clause to Retrieve Data Retrieving Data in XML Format How SQL Server Generates XML Using the.

  • Published on
    04-Jan-2016

  • View
    213

  • Download
    0

Transcript

  • Retrieving XML Data from SQL server

  • Using the FOR XML Clause to Retrieve DataRetrieving Data in XML FormatHow SQL Server Generates XMLUsing the FOR XML ClauseUsing RAW Mode to Retrieve XMLUsing AUTO Mode to Retrieve XMLDemo: Using RAW and AUTO

  • Retrieving Data in XML FormatRetailer

  • How SQL Server Generates XMLSELECT FROM WHERE FOR XML MODE

  • Using the FOR XML ClauseThe FOR XML Clause Syntax

    XML Document FragmentsSELECT select_listFROM table_sourceWHERE search_conditionFOR XML RAW | AUTO | EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]

  • Using RAW Mode to Retrieve XMLGeneric element produced for each row returned by the querySELECT OrderID, OrderDateFROM OrdersFOR XML RAW

  • Using AUTO Mode to Retrieve XMLTable name(s) are used to name elementsSELECT OrderID, OrderDateFROM OrdersFOR XML AUTO

  • Demonstration: Using RAW and AUTO

  • Controlling XML OutputSpecifying the ELEMENTS OptionWorking with Multiple TablesDemo: Retrieving Data from Multiple TablesSpecifying the XMLDATA OptionRetrieving Binary Data

  • Specifying the ELEMENTS OptionColumns are returned as child elementsSELECT OrderID, OrderDateFROM OrdersFOR XML AUTO, ELEMENTS

    10248 07/04/1996

    10249 07/05/1996

  • Working with Multiple TablesSELECT OrderForm.OrderID, Item.ProductID Item.QuantityFROM Orders OrderForm JOIN [Order Details] ItemON OrderForm.OrderID = Item.OrderIDORDER BY OrderForm.OrderIDFOR XML RAW

    SELECT OrderForm.OrderID, Item.ProductID Item.QuantityFROM Orders OrderForm JOIN [Order Details] ItemON OrderForm.OrderID = Item.OrderIDORDER BY OrderForm.OrderIDFOR XML AUTO

  • Demonstration: Retrieving Data from Multiple Tables

  • Specifying the XMLDATA OptionXML-Data Reduced (XDR) Schema pre-pended to resultsSELECT OrderID, OrderDateFROM OrdersFOR XML AUTO, XMLDATA

  • Retrieving Binary DataSELECT EmployeeID, PhotoFROM EmployeesWHERE EmployeeID = 10FOR XML AUTO

    SELECT EmployeeID, PhotoFROM EmployeesWHERE EmployeeID = 1FOR XML AUTO, BINARY BASE64

  • Generating Custom XML FormatsCustom XML FormatsUniversal TablesUsing EXPLICIT ModeDemonstration: Universal Tables and XMLUsing EXPLICIT Mode with Multiple Tables

  • Custom XML FormatsColumns in a table that are mapped to an XML element can be represented as:Element valuesAttributesChild elements

    1996-07-04T00:00:00 Queso Cabrales Singaporean Fried Mee

  • Universal TablesTabular representation of an XML documentTag and Parent columns determine hierarchyColumn names determine element / attribute mapping

    TagParentInvoice!1!InvoiceNoInvoice!1!Date!ElementLineItem!2!ProductIDLineItem!21NULL102481996-07-04T00:00:00NULLNULL2110248NULL11Queso Cabrales2110248NULL42Singaporean

  • Using EXPLICIT ModeConstruct the Transact-SQL to generate the universal tableAdd the FOR XML EXPLICIT clauseSELECT 1 AS Tag, NULL AS Parent, OrderID AS [Invoice!1!InvoiceNo], OrderDate AS [Invoice!1!Date!Element]FROM OrdersWHERE OrderID = 10248FOR XML EXPLICIT

    1996-07-04T00:00:00

  • Using EXPLICIT Mode with Multiple TablesUse the UNION ALL operator to retrieve multiple Tag valuesSELECT 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 OrdersWHERE OrderID=10248UNION ALLSELECT 2 AS Tag,1 AS Parent,OD.OrderID,NULL,OD.ProductID,P.ProductNameFROM [Order Details] OD JOIN Orders O ON OD.OrderID=O.OrderIDJOIN Products P ON OD.ProductID = P.ProductIDWHERE OD.OrderID=10248ORDER BY [Invoice!1!InvoiceNo], [LineItem!2!ProductID]FOR XML EXPLICIT

  • Demonstration: Universal Tables and XML

  • Best PracticesUse RAW Mode for Aggregated DataUse AUTO Mode for Attribute-Centric or Element-Centric XMLUse Aliases with AUTO and RAW to Name XML Elements and AttributesUse an ORDER BY Clause when Querying Multiple TablesUse EXPLICIT Mode for XML Documents Containing a Mix of Attribute-Centric and Element-Centric Mappings

Recommended

View more >