SQLXML Technology For SQL Server Brian Moore Developer

SQLXML Technology For SQL Server Brian Moore Developer and Platform Strategy Group Microsoft Corporation

Agenda l l SQLXML Overview SQLXML Technologies Bulkload Data with SQLXML Web Services with SQLXML

Converging Data Models SQL Server Row. Set XPath XML View XML Files XML

SQLXML Overview l Provides a rich XML view of relational data Ø l l l Benefits of XML – extensible, platform independent format of your data Semi-structured, hierarchical view of flat relational data Two-way access – query and update Multiple access mechanisms Ø HTTP, ADO. NET, SOAP

SQLXML Technologies l T-SQL Language Extensions Ø FOR XML § Ø Open. XML § l Work with your relational database as if it was XML file (through annotated schema) Updategrams/Diffgrams Ø l Rowset provider for in-memory XML documents XML Views and XPath Ø l SQL language extension to retrieve XML instead of rowsets Update database through XML View Bulkload Ø Shred large XML files into existing tables

Configuring SQLXML

Agenda l l SQLXML Overview SQLXML Technologies Bulkload Data with SQLXML Web Services with SQLXML

SQLXML Technologies l l FOR XML Language Extension HTTP Access via URL Ø Ø Ø l l SQL Query Templates XPath Query ADO / OLEDB SQLXML Managed Classes Updategram/Diffgram/Bulkload SOAP

FOR XML Query l T-SQL Language Extension SELECT… FROM… WHERE… ORDER BY… FOR XML (RAW | AUTO [, ELEMENTS] | NESTED [, ELEMENTS] | EXPLICIT) [, XMLData] [, BINARY base 64])

Using FOR XML l Using FOR XML syntax from SQL Managed Classes Sql. Connection Conn = new Sql. Connection(Conn. Str); Sql. Command cmd = new Sql. Command("SELECT * FROM Authors FOR XML AUTO", Conn); Conn. Open(); Xml. Reader r = cmd. Execute. Xml. Reader();

HTTP Access Via URL l URL Query http: //localhost/demos? sql=SELECT * FROM Customers FOR XML AUTO &root=Customer. List l Template http: //server/vroot/vname/template. xml? params l XML View http: //server/vroot/vname/schema. xsd/Customer[@ID='ALFKI']? params

XML Templates <root xmlns: sql="urn: schemas-microsoft-com: xml-sql" sql: xsl="[path to XSLT file]" > <sql: header> <sql: param name="state">WA</sql: param> </sql: header> <sql: query> SELECT Company. Name, Contact. Name, Phone FROM Customers AS Customer WHERE Region LIKE @state FOR XML AUTO </sql: query> <sql: xpath-query mapping-schema="nwind. xsd"> /Customers[@Region=$state] </sql: xpath-query> </root>

XML Views l l l Map between relational data and XML Declarative Noninvasive Ø Ø l No changes to legacy data sources No control over DB Server required XML View is an XML Schema Ø Ø XDR for SQL Server 2000 and SQLXML 1. 0 XSD for SQLXML 2. 0 and beyond

XML Views Default Mapping Example <xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema" xmlns: msdata="urn: schemas-microsoft-com: mapping-schema"> <xsd: element name="Customers"> <xsd: complex. Type> <xsd: attribute name="Cust. ID"/> </xsd: complex. Type> </xsd: element > </xsd: schema> XML View <Customers Cust. ID="A" /> <Customers Cust. ID="B" /> Relational Data Customers Table Cust. ID A NULL B

XML Views Explicit Mapping Example <xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema" xmlns: msdata="urn: schemas-microsoft-com: mapping-schema"> <xsd: element name="Customers" msdata: relation="tbl. Customers"> <xsd: complex. Type> <xsd: attribute name="ID" msdata: field="str. Cust. ID" /> </xsd: complex. Type> </xsd: element > </xsd: schema> XML View <Customers ID="A" /> <Customers ID="B" /> Relational Data tbl. Customers Table str. Cust. ID A NULL B

Join Example <xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema" xmlns: msdata="urn: schemas-microsoft-com: mapping-schema"> <xsd: element name="Customer" msdata: relation="Customers"> <xsd: complex. Type> <xsd: sequence> <xsd: element name="Order" msdata: relation="Orders"> <xsd: annotation> <xsd: appinfo> <msdata: relationship parent="Customers" parent-key="Customer. ID" child="Orders" child-key="Customer. ID" /> </xsd: appinfo> </xsd: annotation> <xsd: complex. Type> <xsd: attribute name="Order. Date" type="xsd: date. Time" /> </xsd: complex. Type> </xsd: element> </xsd: sequence> <xsd: attribute name="Customer. ID" /> </xsd: complex. Type> </xsd: element> </xsd: schema>

XPath l l l Use XPath to query SQL Database as if it was an XML file Each query translates into a SQL statement XPath /Customer/Order[@Order. ID=‘ 10692’]

SQLXML Technologies

Agenda l l SQLXML Overview SQLXML Technologies Bulkload Data with SQLXML Web Services with SQLXML

Application Scenario Business to Business Data Interchange Bu lkl oa d lts su Re Annotations = Mapping Schema Qu ery XSD Schema + SQL Server + XPATH XML /Customers Send

XML Bulkload l l l Bulkload XML documents into a SQL Server database via the XML View Just like traditional bulkload, except supports hierarchical relationships and semi-structured data Scriptable COM object Ø l l Requires P/Invoke in. NET Performance is about 75% of traditional bulkload Can use or ignore database constraints

Bulkload Data with SQLXML

Agenda l l SQLXML Overview SQLXML Technologies Bulkload Data with SQLXML Web Services with SQLXML

Web Services with SQLXML l l WSDL file describing each SPROC or template exposed Tool to choose which SPROC or template to expose IIS/ISAPI WSDL Client SOAP Message SP Message Template SQL Server

Web Services with SQLXML

Call To Action l Take the next step with SQL Server Web Services for your application Ø l Download SQLXML 3. 0 SP 2 Ø l http: //www. microsoft. com/sql/downloads/ For more information, please email Ø l Meet with the Microsoft technical team to discuss an Architectural Design Session or a Proof of Concept at the Microsoft Technology Center scdlite@microsoft. com Slides available at Ø Ø http: //www. microsoft. com/usa/southcentral http: //www. elysiumsystems. com

SQL Server Summit Brought To You By: