Introduction to SQLXML 3'0

1
Introduction to SQLXML 3.0

• ? ? ?
• Developer Evangelist
• Developer Division
• Microsoft Corporation

2
??

• XML ? ??? ???
• XML Everywhere
• SQL ? ??? XML ??
• FOR XML
• XML View

3
Introd. to .NET Framework
??? ???
ADO.NET
????
HTML, XML
WebServer
HTTP Handler /ASP.NET
SOAP,XML
.NET RMI / SOAP
World!
ADO.NET / XML / XML Schema
COM Server
CLR
OS
4
XML? ??? ???!

• XML? ??.
• XML? ??? ? ????.
• XML? ?????? ??? ??.
• XML? ???. ?
• XML? ?? ??? ????? ??.
• .
• Microsoft ? XML ??? ??? ???.
• JAVA ? XML? ??? ??? ???.

5
The problem

• ??? ???? ?? ??? ???
• ??? ?? hardwired connector ? ??
• ??(connectivity)? ??(integration)? ???? ??

6
The solution XML
???
?? ??
?? ???
Extranet
Intranet
?? ???
Web site
Web site
???
?? ??
??
7
XML Everywhere

• BizTalk Orchestration

Document(Office, Excel, Shared Portal)
System.Xml
Messaging (Web Service, .NET Remoting,
Serialization)
Data(ADO.NET, SQL 2000)
8
XML ? ??? ???

• ??? ??? (System.String)
• XML ?? (System.Xml)
• ADO.NET ()
• T-SQL ()
• SQLOLEDB / SQLXMLOLEDB ()
• Office Developer
• Exchange Server 2000
• BizTalk

9
SQLXML? ??? ???

• XML Schema
• XPath
• XSLT
• XQuery

10
XML Schema

• XML ??? ??? ??(element)? ??(attribute)
• ? ??? ??? ??? ?
• XML ??? ??? ??? ??
• XML ??? ??? ??? ??? ??
• ??? ??? ??,?? ?
• ?? ??? ??,?? ?? ??
• XML ??? ??? ??

11
XPath/XQuery

• XML ?? ?? ??
• XML ?? ?? (DOM) ??
• XPath
• /Customer/Order_at_OrderID10692
• XQuery
• For i in sqltable('Customers', 'CustomerID')
  Return ltCustomer ID i/_at_CustomerID
  Name i/_at_ContactName/gt

12
XSLT

• XML ??? HTML ??? ??
• XML ??? WAP ??? ??
• XML ??? ? ?? XML ??? ??

13
The Two Worlds
SQL
Language
SQL Server
Data storage
RowSet
Data output
Relational world
14
SQL 2000 Server XML ? ??

• RDB? ??? ???? XML? ??
• XML? RDB? ??? ???? ??
• XML-Relation Mapping Layer

15
.NET vs. SQLXML

• .NET
• XML-Object Mapping Layer
• XML Serialization
• XML-Relation Mapping Layer
• ADO.NET
• SQLXML
• XML-Relation Mapping Layer

16
SQLXML ???? Overview (1)
?????
?? ??
SQL Server
SQL Server
IIS
ISAPI
SQLOLEDBSQLXMLOLEDBSQLXML Managed Provider
HTTP

• Translation of
• Updategrams
• Query templates
• XPath queries

• SQL executed
• Open XML
• T-SQL
• Update, Insert, Delete
• SelectFor XML

Query request
Updategram
Annotated Schemas
.NET Component
ADO.NET
17
SQLXML ???? Overview (2)
18
FOR XML Query

• SQL ?? ??
• SELECT
• FROM
• WHERE
• ORDER BY
• FOR XML ( raw
• auto , ELEMENTS
• nested , ELEMENTS
• explicit)
• , XMLData
• , BINARY base64)

19
Using FOR XML

• SqlConnection Conn new SqlConnection(ConnStr)
• SqlCommand cmd new SqlCommand(select from
  Authors FOR XML AUTO, Conn)
• Conn.Open()
• XmlReader r cmd.ExecuteXmlReader()

20
For XML Raw Mode

• ? ???? ??? ltrowgt ?? (element)
• ?? ??? ??? ??
• ? column/value? ltrowgt ?? ?? ??(attribute)/value?
  ??
• XML ??? ??? ??? ??
• ltrow CustomerIDALFKI OrderID10643 /gt
• ltrow CustomerIDALFKI OrderID10692 /gt
• ltrow CustomerIDANATR OrderID10308 /gt
• ltrow CustomerIDFISSA /gt

??(attribute)
??(element)
21
For XML Auto Mode

• ?? XML ??
• ???/? ?? -gt ?? ??
• Column ?? -gt ?? ??
• Alias ? ?? ?? ?? ??
• ltCustomers CustomerIDALFKIgt
• ltOrders OrderID10643 /gt
• ltOrders OrderID10692 /gt
• lt/Customersgt
• ltCustomers CustomerIDANATRgt
• ltOrders OrderID10308 /gt

22
For XML Explicit Mode

• SELECT 1 as TAG,
• NULL as PARENT, CustomerID AS
  Customer!1!cid!id, CompanyName AS
  Customer!1!name!element,
• NULL AS Order!2!oid!id
• FROM Customers
• WHERE CustomerID 'ALFKI' OR CustomerID'BOLID'
• UNION ALL
• SELECT 2,
• 1,
• Customers.CustomerID,
• NULL,
• 'O-'CAST(Orders.OrderID AS varchar(32))
• FROM Customers INNER JOIN Orders
• ON Customers.CustomerIDOrders.CustomerID
• WHERE Customers.CustomerID 'ALFKI'
• OR Customers.CustomerID'BOLID'
• ORDER BY Customer!1!cid!id
• FOR XML explicit

23
FOR XML ? ??

• RAW
• XML ??? ??? ??
• AUTO
• ?? ??? ??? XML
• NESTED
• ?? ??? ??? XML
• EXPLICIT
• ?????, ???

24
AUTO vs. Nested

• NESTED
• XML ??? ?? ????? ??? ??
• AUTO
• XML ??? ?? ?? ??? ??
• NESTED
• ?? ???? ?/alias ?? ?? ??? ??? ??
• Aggregation, GROUP BY ??
• ?? ??? ?? ???? ??

25
XML View

• ??? ???? XML ?? mapping ??.
• DB ??? ??? ??? ???? ??
• XML Schema ? ??
• XDR
• XSD (SQLXML 2.0 ??)

26
?) Default Mapping

• ltxsdschema xmlnsxsd"http//www.w3.org/2001/XMLS
  chema
• xmlnsmsdata"urnschemas-microsoft-commappin
  g-schemagt
• ltxsdelement nameCustomersgt
• ltxsdcomplexTypegt
• ltxsdattribute nameCustID/gt
• lt/xsdcomplexTypegt
• lt/xsdelement gt
• lt/xsdschemagt

??? ??? Customers ???
XML View
CustID ------ A NULL B
ltCustomers CustIDA/gt ltCustomers/gt ltCustomers
CustIDB/gt
27
?) Explicit Mapping

• ltxsdschema xmlnsxsd"http//www.w3.org/2001/XMLS
  chema
• xmlnsmsdata"urnschemas-microsoft-commappin
  g-schemagt
• ltxsdelement nameCustomers
  msdatarelationtblCustomers"gt
• ltxsdcomplexTypegt
• ltxsdattribute nameID msdatafieldpkCustID"
  /gt
• lt/xsdcomplexTypegt
• lt/xsdelement gt
• lt/xsdschemagt

??? ??? tblCustomers ???
pkCustID ------ A NULL B
ltCustomers IDA/gt ltCustomers/gt ltCustomers
IDB/gt
28
?) Join Mapping

• ltxsdschema xmlnsxsd"http//www.w3.org/2001/XML
  Schema"
• xmlnsmsdata"urnschemas-microsoft-commapp
  ing-schema"gt
• ltxsdelement name"Customer" msdatarelation"Cu
  stomers"gt
• ltxsdcomplexTypegt
• ltxsdsequencegt
• ltxsdelement name"Order"
  msdatarelation"Orders"gt
• ltxsdannotationgtltxsdappinfogt
• ltmsdatarelationship
• parent"Customers"
  parent-key"CustomerID"
• child"Orders"
  child-key"CustomerID" /gt
• lt/xsdappinfogtlt/xsdannotationgt
• ltxsdcomplexTypegt
• ltxsdattribute name"OrderDate"
  type"xsddateTime"/gt
• lt/xsdcomplexTypegt
• lt/xsdelementgt
• lt/xsdsequencegt
• ltxsdattribute name"CustomerID" /gt
• lt/xsdcomplexTypegt
• lt/xsdelementgt

29
XPath/XQuery

• ?? XML ?? ?? ??? XPath/XQuery ??? ??? ? ?? ??
• Each query translates into a SQL statement
• XPath
• /Customer/Order_at_OrderID10692
• XQuery
• For i in sqltable('Customers', 'CustomerID')
  Return ltCustomer ID i/_at_CustomerID
  Name i/_at_ContactName/gt

30
Using XPath

• SqlXmlCommand cmd new SqlXmlCommand(ConnStr)
• cmd.CommandType SqlXmlCommandType.XPath
• cmd.CommandText /Customer_at_CustIDALFKI
• cmd.SchemaPath nwind.xml
• cmd.RootTag ROOT
• cmd.XslPath Customers.xsl
• XmlReader r cmd.ExecuteXmlReader()

31
FOR XML vs. XML View

• ?? ?? ? ???? ?? ??? XML ? ??
• ????? XML View ? ??

32
(No Transcript)