XML Support in SQL Server 2000

1
XML Support in SQL Server 2000
Sriram Krishnan Kevin Menard
2
SQL Server - XML

• SQL Server 2000 is an XML-enabled DBMS
• It can read and write XML data
• It can return data from databases in XML format
• It can read and update data stored in XML
  documents

3
SQL Server's XML Features

• FOR XML
• An extension to SELECT - allows result sets as
  XML
• OpenXML
• Allows reading and writing of data in XML
  documents
• XPath queries
• Allows SQL Server databases to be queried using
  XPath syntax

• Schemas
• Supports XDR mapping schema and XPath queries
  against them
• Updategrams
• XML templates for data modifications
• XML Bulk Load
• A high-speed facility for loading XML data into a
  SQL Server

4
SELECT FOR XML

• FOR XML causes SELECT to return query results as
  an XML stream
• Three formats RAW, AUTO, or EXPLICIT
• SELECT column list FROM table list WHERE filter
  criteria FOR XML RAW AUTO EXPLICIT ,
  XMLDATA , ELEMENTS , BINARY BASE64

5
FOR XML -- Raw Mode

• SELECT CustomerId, CompanyName FROM Customers FOR
  XML RAW
• XML_F52E2B61-18A1-11d1-B105-00805F49916B
• -------------------------------------------------
  -----------------
• ltrow CustomerId"ALFKI" CompanyName"Alfreds
  Futterkiste"/gt
• ltrow CustomerId"ALMRT" CompanyName"Antonio
  Moreno Taquer'a"/gt
• ltrow CustomerId"BERGS" CompanyName"Berglunds
  snabbköp"/gt
• Columns ? Attributes
• Rows ? Generic row element
• XML returned is not well-formed
• Lacks a root element must be generated by the
  client

6
FOR XML -- Auto Mode

• SELECT CustomerId, CompanyName FROM Customers FOR
  XML AUTO
• XML_F52E2B61-18A1-11d1-B105-00805F49916B
• -------------------------------------------------
  -----------------
• ltCustomers CustomerId"ALFKI"
  CompanyName"Alfreds Futterkiste"/gt
• ltCustomers CustomerId "ALMRT"
  CompanyName"Antonio Moreno Taquer'a"/gt
• ltCustomers CustomerId"BERGS"
  CompanyName"Berglunds snabbköp"/gt
• Each row in the result set is named after the
  table or view
• For results with more than one row, this amounts
  to having more than one top-level (root) element
  in the fragment, which isn't allowed in XML
• The rows from joined tables are nested within one
  another.

7
FOR XML -- Auto Mode (cont.)

• Example of a JOIN query
• SELECT Customers.CustomerID, CompanyName,
  OrderId FROM Customers JOIN Orders ON
  (Customers.CustomerIdOrders.CustomerId) FOR XML
  AUTO
• XML_F52E2B61-18A1-11d1-B105-00805F49916B
• -------------------------------------------------
  ----------------- ltCustomers CustomerID"ALFKI"
  CompanyName"Alfreds Futterkiste"gt
• ltOrders OrderId"10643"/gt
• ltOrders OrderId"10702"/gt
• ltOrders OrderId"10952"/gt
• lt/Customersgt
• ltCustomers CustomerID"ANATR" CompanyName"Ana
  Trujillo Emparedado ltOrders OrderId"10308"/gt
• ltOrders OrderId"10759"/gt
• lt/Customersgt
• ltCustomers CustomerID"FRANR" CompanyName"France
  restauration"gt
• ltOrders OrderId"10671"/gt
• ltOrders OrderId"10971"/gt
• lt/Customersgt

8
FOR XML Explicit Mode

• More flexible and more complicated than either
  raw mode or auto mode
• Explicit mode queries define XML documents in
  terms of a universal table format
• A mechanism for describing the format of XML
  document returned
• A universal table is just a SQL Server result set
  with special column headings that tell the server
  how to produce an XML document from your data
• Element!Tag!Attribute!Directive

9
Universal Table Format

• Tag Parent Customers!1!CustomerId Customers!1
  Orders!2!OrderId Orders!2!OrderDate!element
• --------------------------------------------------
  ---------------------------------------
• 1 NULL ALFKI Alfreds Futterkiste NULL
  NULL
• 2 1 ALFKI NULL 10643
  1997-08-25T000000
• 2 1 ALFKI NULL 10692
  1997-10-03T000000
• 1 NULL ANATR Ana Trujillo Empare NULL
  NULL
• 2 1 ANATR NULL 10308
  1996-09-18T000000

10
Explicit Mode Complex Query

• Links the Customers and Orders tables using the
  CustomerId column
• The Tag and Parent values in the second query
  link it to the first
• SELECT 1 AS Tag, NULL AS Parent, CustomerId AS
  Customers!1!CustomerId, CompanyName AS
  Customers!1, NULL AS Orders!2!OrderId, NULL
  AS Orders!2!OrderDate!element FROM Customers
  UNION
• SELECT 2 AS Tag, 1 AS Parent, CustomerId, NULL,
  OrderId, OrderDate FROM Orders ORDER BY
  Customers!1!CustomerId, Orders!2!OrderDate!elem
  ent FOR XML EXPLICIT
• XML_F52E2B61-18A1-11d1-B105-00805F49916B
• --------------------------------------------------
  ----------------
• ltCustomers CustomerId"ALFKI"gtAlfreds Futterkiste
  
• ltOrders OrderId"10643"gt
• ltOrderDategt1997-08-25T000000lt/OrderDategt
• lt/Ordersgt
• ltOrders OrderId"10692"gt
• ltOrderDategt1997-10-03T000000lt/OrderDategt
• lt/Ordersgt
• lt/Customersgt
• ltCustomers CustomerId"ANATR"gtAna Trujillo
  Emparedados y helados
• ltOrders OrderId"10308"gt
• ltOrderDategt1996-09-18T000000lt/OrderDategt

11
OpenXML

• OpenXML is a built-in Transact-SQL function that
  can return an XML document as a rowset
• Syntax
• OpenXML(hdoc, RowPattern , Flag WITH
  SchemaDeclaration TableName
• hdoc Handle to XML Document
• Returned from sp_xml_preparedocument
• RowPattern XPath expression that identifies
  rows
• Flag Attribute or element-centric column
  patterns
• WITH Shredded rowset based upon additional
  parameters (omission of WITH edge table view)

12
OpenXML Example

• DECLARE _at_hDoc int EXEC sp_xml_preparedocument
• _at_hDoc output,
• 'ltsongsgt
• ltartist name"Johnny Hartman"gt
• ltsonggt ltnamegtIt Was Almost Like a
  Songlt/namegtlt/songgt
• ltsonggt ltnamegtI See Your Face Before
  Melt/namegtlt/songgt
• ltsonggt ltnamegtEasy Livinglt/namegtlt/songgt
• lt/artistgt
• ltartist name"Harry Connick, Jr."gt
• ltsonggt ltnamegtSonny Criedlt/namegtlt/songgt
• ltsonggt ltnamegtA Nightingale Sanglt/namegtlt/songgt
• ltsonggt ltnamegtYou Didn't Know Me
  Whenlt/namegtlt/songgt
• lt/artistgt
• lt/songsgt'
• SELECT FROM OPENXML(_at_hdoc, '/songs/artist/song',
  2) WITH
• (artist varchar(30) '../_at_name',
• song varchar(50) 'name')
• EXEC sp_xml_removedocument _at_hDoc

13
XML Mapping Schema

• XML schemas are XML documents that define the
  type of data that other XML documents may contain
• Replacement for DTD
• A mapping schema is a special type of schema that
  maps data between an XML document and a
  relational table
• Can be used to create an XML view of a SQL Server
  table
• SQL Server's XML schema support is based on
  XML-Data Reduced (XDR)
• An XML-Data subset that can be used to define
  schemas

14
Annotated Mapping Schema

• An annotated schema is a mapping schema with
  special annotations (from the XML-SQL namespace)
  that link elements and attributes with tables and
  columns
• Table ? element (default)
• Column ? attribute (default)
• Provides same level of granularity as FOR XML
  EXPLICIT, without having to use universal tables

15
Annotated Mapping Schema (cont.)

• Example Schema
• lt?xml version"1.0"?gt
• ltSchema name"customers" xmlns"urnschemas-micros
  oft-comxml-data" xmlnssql"urnschemas-microsoft
  -comxml-sql"gt
• ltElementType name"Customer sqlrelation"Custome
  rs"gt
• ltAttributeType name"CustomerNumber
  sqlfield"CustomerId"/gt
• ltAttributeType name"Name" sqlfield"CompanyName"
  /gt
• lt/ElementTypegt
• lt/Schemagt

16
Querying Using XPath

• XPath is a tree navigation language defined by
  W3C
• SQL Server uses XPath to select data from XML
  views provided by annoted schema
• http//localhost/Northwind/Schema/Customer.XDR/Cus
  tomer_at_IdA25
• XPath query can be passed via URL or template or
  via SQLOLEDB provider

17
Updategrams

• Updategrams provide an XML-based method to update
  database
• Templates with special attributes and elements
• Specify the data to update, how to update it
• All the execution mechanisms available with
  templates work equally well with updategrams
• POST, save to file and execute via URL, via ADO

18
Updategrams (cont.)

• Each updategram
• Contains the data changes in the form of before
  and after elements.
• Before element contains the before image of the
  data to be changed
• Row deletions
• Have before image but no after image
• Row Insertions
• Have an after image but no before image

19
Updategrams (cont.)

• lt?xml version"1.0"?gt
• ltemployeeupdate xmlnsupdg "urnschemas-microsoft
  -comxml-updategram"gt
• ltupdgsyncgt
• ltupdgbeforegt
• ltEmployees EmployeeID"4"/gt
• lt/updgbeforegt
• ltupdgaftergt
• ltEmployees City"Scotts Valley" Region"CA"/gt
  lt/updgaftergt
• lt/updgsyncgt
• lt/employeeupdategt
• Updategrams can also be parameterized
• ltupdgheadergt
• ltupdgparam name"OrderID"/gt
• ltupdgparam name"ShipCity"/gt
• lt/updgheadergt

20
Updategrams (cont.)

• Updategrams can also use XDR mapping schemas
• lt?xml version"1.0"?gt
• ltorderupdate xmlnsupdg "urnschemas-microsoft-co
  mxml-updategram"gt
• ltupdgsync updgmapping-schema"OrderSchema.xml"gt
• ltupdgbeforegt ltOrder OID"10248"/gt lt/updgbeforegt
• ltupdgaftergt ltOrder City"Reims"/gt lt/updgaftergt
• lt/updgsyncgt lt/orderupdategt
• Where the XDR schema is given by
• lt?xml version"1.0"?gt
• ltSchema xmlns"urnschemas-microsoft-comxml-data"
  xmlnssql"urnschemas-microsoft-comxml-sql"gt
• ltElementType name"Order" sqlrelation"Orders"gt
• ltAttributeType name"OID"/gt ltAttributeType
  name"City"/gt
• ltattribute type"OID" sqlfield"OrderID"/gt
• ltattribute type"City" sqlfield"ShipCity"/gt
• lt/ElementTypegt lt/Schemagt

21
XML Bulk Load

• Updategrams and OpenXML, are not suitable for
  loading large amounts of data
• SQLXML provides a facility called the XML Bulk
  Load
• COM object
• The first step in using the XML Bulk Load is to
  define a mapping schema that maps the XML data to
  tables and columns in database
• When the component loads the XML data, it will
  read it as a stream and use the mapping schema to
  decide where the data goes in the database

22
XML Bulk Load (cont.)

• VB Example
• Set objBulkLoadCreateObject("SQLXMLBulkLoad.SQLXM
  LBulkLoad")
• objBulkLoad.ConnectionString "providerSQLOLEDB
  data sourceSuperServerdatabaseNorthwind"
• objBulkLoad.Execute "d\xml\OrdersSchema.xdr",
  "d\xml\OrdersData.xml"
• Set objBulkLoad Nothing

23
Accessing SQL Server Over HTTP

• SQL Server's ability to publish data over HTTP is
  made possible through SQLISAPI with IIS
• An Internet Server API (ISAPI) extension
• SQLISAPI uses SQLOLEDB, SQL Server's native OLE
  DB provider, to access the database associated
  with a virtual directory
• Configuring a virtual directory allows SQL
  Server's XML features via HTTP

24
Accessing SQL Server over HTTP (cont.)

• Private Intranet
• Send a SELECT FOR XML query string in URL
• Post an XML query template to SQLISAPI
• Public Internet
• Specify a server-side XML schema
• Specify a server-side XML query template

25
URL Queries

• URL queries allow users to specify a complete
  Transact-SQL query via a URL
• http//localhost/Northwind?sqlSELECTFROMCusto
  mersWHERECustomerId'ALFKI'ORCustomerId'ANATR
  'FORXMLAUTO rootCustomerList
• The first parameter we pass here is sql
• The second parameter specifies the name of the
  root element for the XML document that will be
  returned

26
URL Queries (cont.)

• URL query can also include the xsl parameter
• Translates the XML document that's returned by
  the query into a different format
• http//localhost/Northwind?sqlSELECTCustomerId,
  CompanyNameFROMCustomersFORXMLAUTOrootCusto
  merListxslCustomerList.xsl
• http//localhost/Northwind?sqlSELECTCustomerId,
  CompanyNameFROMCustomersFORXMLAUTOrootCusto
  merListxslCustomerList.xslcontenttypetext/xml

27
Executing Stored Procedures via URL

• Stored Procedure
• CREATE PROC ListCustomersXML _at_CustomerId
  varchar(10)'', _at_CompanyName varchar(80)'' AS
• SELECT CustomerId, CompanyName FROM Customers
  WHERE CustomerId LIKE _at_CustomerId AND CompanyName
  LIKE _at_CompanyName FOR XML AUTO
• URL for executing stored procedure
• http//localhost/Northwind?sqlEXECListCustomersX
  ML _at_CustomerId'A25',_at_CompanyName'An25'rootC
  ustomerList

28
Template Queries

• Templates are XML documents based on the XML-SQL
  namespace
• Mechanism for translating a URL into a query that
  SQL Server can process
• Safer and more widely used technique for
  retrieving data over HTTP
• End users never see the source code
• Templates are stored on the Web server
• Referenced via a virtual name

29
Sample Template

• lt?xml version'1.0' ?gt
• ltCustomerList xmlnssql'urnschemas-microsoft-com
  xml-sql'gt
• ltsqlquerygt
• SELECT CustomerId, CompanyName FROM Customers FOR
  XML AUTO
• lt/sqlquerygt
• lt/CustomerListgt
• Example invocation
• http//localhost/Northwind/templates/CustomerList.
  XML
• Specify a style sheet to apply to a template
  query
• http//localhost/Northwind/Templates/CustomerList3
  .XML?xslTemplates/CustomerList3.xslcontenttypet
  ext/html

30
Templates

• Parameterized Templates
• Permit the user to supply parameters to the query
  
• lt?xml version'1.0' ?gt
• ltCustomerList xmlnssql'urnschemas-microsoft-com
  xml-sql'gt
• ltsqlheadergt ltsqlparam name'CustomerId'gtlt/sqlp
  aramgtlt/sqlheadergt
• ltsqlquerygt SELECT CustomerId, CompanyName FROM
  Customers WHERE CustomerId LIKE _at_CustomerId FOR
  XML AUTO lt/sqlquerygt
• lt/CustomerListgt
• Example invocation
• http//localhost/Northwind/Templates/CustomerList2
  .XML? CustomerIdA25

31
Conclusions

• SQL Server 2000 has a lot of ways to work with
  XML, suitable for a number of situations
• Questions?
• References
• Conrad, Andrew. A Survey of Microsoft SQL Server
  2000 XML Features Microsoft Corporation, 2001
• http//msdn.microsoft.com/xml/default.aspx?pull/l
  ibrary/en-us/dnexxml/html/xml07162001.asp
• Henderson, Ken. Guru's Guide to SQL Server
  Architecture and Internals, The (Chapter Using
  SQL Server's XML Support)
• Rys, Michael. Bringing the Internet to Your
  Database Using SQL Server 2000 and XML to Build
  Loosely-Coupled Systems Microsoft Corporation