WEB 331 Developing XML Driven Applications With SQL

WEB 331: Developing XML Driven Applications With ™ SQL Server 2000 Andrew Conrad Software Design Engineer Webdata Group Microsoft Corporation aconrad@microsoft. com Michael Brundage Software Design Engineer Webdata Group Microsoft Corporation michaelb@microsoft. com

Overview Of Talk l Goals of presentation: Ø Ø l Agenda: Ø Ø Ø l Learn about the XML View Examine application scenarios A survey of the SQL Server features that provide the XML View Discuss strengths and weaknesses of each feature See the features in action Questions welcome at any time

Why are you still at Teched? A) If I stay till Friday morning – Three Day Weekend!

Why are you still at Teched? A) If I stay till Friday morning – Three Day Weekend! B) More snacks and treats.

Why are you still at Teched? A) If I stay till Friday morning – Three Day Weekend! B) More snacks and treats. C) I really like the weather in Atlanta!

Why are you still at Teched? A) If I stay till Friday morning – Three Day Weekend! B) More snacks and treats. C) I really like the weather in Atlanta! D) No Clue.

Why are you still at Teched? A) If I stay till Friday morning – Three Day Weekend! B) More snacks and treats. C) I really like the weather in Atlanta! D) No Clue. E) I am interested in learning how to develop a data driven application using the “XML View” provided by XML for SQL Server.

What Is The “XML View” ? l l l The XML view is a virtual collection of XML documents created over a SQL Server database The XML view can completely encapsulate and hide the underlying relational structures The resulting XML documents can easily represent hierarchical, semi-structured content

Searchable Shapes Database

The XML View – Architecture SQL Server XML Templates + XSLT XPath Queries Relation tables Updategrams XML Bulkload Annotated XSD Mapping Schemas For XML Queries Relation views Relation Stored procs

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> </root>

XML Templates

Annotated Mapping Schemas l l l Defines an XML View of a database Uses XSD or XDR schemas with special annotations Annotations specify the mapping between xml and relational data XPath is used to query the XML View Changes to an XML View are persisted to database via Updategrams

Tables And Columns l Default mapping: Ø Ø l Elements map to tables of the same name Attributes map to columns in the table Explicit mapping: Ø Ø sql: relation maps to a table sql: field maps to a column

Default Mapping <xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema“> <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

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

Join Relationships l Require an explicit annotation to describe the join: < sql: relationship parent=“Table 1" parent-key=“pk" child=“Table 2” child-key=“fk" /> l Expresses the SQL join SELECT * FROM Table 1 JOIN Table 2 ON Table 1. pk= Table 2. fk

Join Example <xsd: schema xmlns: xsd="http: //www. w 3. org/2001/XMLSchema" xmlns: sql="urn: schemas-microsoft-com: mapping-schema"> <xsd: element name="Customer" sql: relation="Customers“ > <xsd: complex. Type> <xsd: sequence> <xsd: element name="Order" sql: relation="Orders“ > <xsd: annotation> <xsd: appinfo> <sql: 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>

Some Other Annotations Ignore schema components: <xsd: attribute name=“Not. In. The. DB” sql: mapped=“ 0” /> l Query on field which is not returned in results: <xsd: attribute name=“Hidden. ID” sql: hide=“ 1” /> l Introduce XML elements that do not map to the database: <xsd: element name=“Order. List” sql: is-constant=“ 1” > <xsd: complex. Type> … </xsd: complex. Type> </xsd: element> l

Can Even Map Open Content l Map SQL data as XML text: <xsd: element name=“E” sql: overflow-field=“Column” /> <E> <Overflow. Stuff> <A b=“c”/> </Overflow. Stuff> </E> Column -----<Overflow. Stuff> <A b=“c”/> </Overflow. Stuff>

XPath l l Navigation language (used by XSL to select nodes in an XML document) Compact syntax resembles file paths or URLs: /Customer/Order[@Order. ID=‘ 10692’] Each XPath translates into a SQL query using FOR XML EXPLICIT to construct the XML For complete details on XPath, see the W 3 C specification at: http: //www. w 3. org/TR/XPath

XPath And The XML View In Real Life l l l The shape of the generated XML instance document can be very different then the underlying relation storage Multiple databases (with different schemas) can provide a common XML view Much easier to develop and maintain than For XML Explicit queries

Annotated Mapping Schema and XPath

Updategrams And XML Views l l Modify the database through the XML View Declare before and after images of the XML (what it is now, and what you want it to be) Optimistic concurrency control ensures that the transaction takes place only if the before image matches the current database state Each updategram is translated into one or more INSERT, UPDATE, and DELETE statements, carried out as a single transaction

Updategrams And XML Views l Updategram in an XML template: <updategram-example> <updg: sync mapping-schema=“schema. xsd” xmlns: updg=“urn: schemas-microsoft-com: xml-updategram”> <updg: before> <Customer. ID=“LAZYK” Address=“ 12 Orchestra Terrace”> <Order. ID=“ 27” Unit. Price=“ 100” Quantity=“ 1000” /> </Customer> </updg: before> <updg: after> <Customer. ID=“LAZYK” Address=“ 8 Opera Court”/> </updg: after> </updg: sync> </updategram-example>

Updategrams In Real Life l l l Enables persisting of changes to XML view by disconnected clients Can easily represent hierarchical changes to data Existing programming model is limited, client must generate updategrams manually

XML Update. Grams

XML Bulkload And XML Views 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 Performance is about 75% of traditional bulkload

XML Bulkload And XML Views l Sample Bulkload vbs script: set obj. BL = Create. Object("SQLXMLBulk. Load") obj. BL. Connection. String = "provider=SQLOLEDB. 1; data source=(local); database=tempdb; uid=sa; pwd=" obj. BL. Error. Log. File = "error. xml" obj. BL. XMLFragment = True obj. BL. Check. Constraints = False obj. BL. Execute "schema. xsd", "data. xml" Set obj. BL = Nothing

XML Bulkload In Real Life l l l Very fast, efficient way to shred XML and insert in to a SQL Server database XML Views allows the publisher and consumer of the data to have different database schemas Currently, does not support “max errors” so bulkload processing is stopped by first problem

XML Bulkload

XML View Application Scenario – A Data Driven Web Site l Problem: Ø Ø l Need to publish data driven web site with minimal coding Control of SQL Server database api (table, stored procs, views) is limited Solution: Ø Ø Define an XML View and apply XSLT to the instance documents to generate HTML User communicates with web site through template parameters

XML View Application Scenario – Data Interchange: l Problem: Ø Ø l Want to exchange large amounts of data with other applications Although logical business objects are similar, database schemas are different Solution: Ø Ø Run XPath queries against XML View to generate data Incrementally load into destination database using XML View (different mappings) and XML Bulkload

XML View Application Scenario – Disconnected Clients: l Problem: Ø Ø l Client application is required to consume data from different SQL Server databases Client may need disconnected, HTTP access to SQL Server Solution: Ø Ø Define common XML View Client queries with XPath, and submits changes via updategrams using SQL Server HTTP access

Applications

XML For SQL Server – Releases l l Version 1. 0 of product shipped as part of SQL Server 2000. Going forward new releases of client functionality will be available via the MSDN web site: http: //www. msdn. microsoft. com/downlo ads/default. asp navigate via TOC to -> Data Access -> Microsoft® SQL Server -> XML for SQL Server 2000

Feedback l l PSS xmlsqlfb@microsoft. com news: microsoft. public. sqlserver. xml E-mail us

Additional Resources l l l MSDN XML Dev Center http: //msdn. microsoft. com/xml http: //www. microsoft. com/sql SQL Server 2000 Documentation (Books Online) Web Release documentation “Professional XML Databases”, Wrox Press