19 21 MARCH 2006 Riyadh, Saudi Arabia

1
19 21 MARCH 2006 Riyadh, Saudi Arabia
2
XML and Web Services support in SQL Server 2005

• Michael StoreyXpertise Training Ltd (UK)

3
Outline

• The XML Data Type
• Schema validated and untyped xml
• XML type indexes
• FOR XML and OpenXML enhancements
• Using XQuery with the XML data type
• Updating the XML data type with XQuery DML
• SQL Server 2005 Web Service Support
• Accessing HTTP endpoints with .NET clients

4
XML in a relational database

• XML can be stored as text
• loses much of value of XML representation
• XML can decomposed into multiple relational
  tables
• allows use of relational technologies
• XML can be stored as an xml data type
• allows use of XML technologies

5
Using the XML data type
CREATE TABLE xml_tab ( the_id INTEGER,
xml_col XML)
CREATE PROCEDURE transform ( _at_x XML, _at_y XML
OUTPUT) AS ...
CREATE FUNCTION simple ( _at_x NVARCHAR(max)) RETUR
NS XML AS DECLARE _at_a XML SET _at_a _at_x ... RETURN _at_a
6
XML column

• XML column can store well-formed XML
• XML 1.0 recommendation
• documents or fragments

7
XML schema in database

• XML schemas used by XML data types must be in
  database
• Create XML SCHEMA COLLECTION
• collection name associated with XML instance
• Create XML SCHEMA COLLECTION requires literal
  schemas

CREATE XML SCHEMA COLLECTION geocoll
literal schema
AS 'ltxsschema ...
targetNamespace
urngeogt
targetNamespace
...
lt/xsschemagt'
CREATE TABLE Locations
reference to
(
schema collection
location xml(geocoll)
...
)
8
Using strongly typed XML
CREATE TABLE point_tab( id int IDENTITY primary
key, -- geocoll include schema for 'urngeo'
namespace thepoint xml(CONTENT geocoll) GO --
this works, schema-valid Point INSERT INTO
point_tab VALUES( 'ltPoint xmlns"urngeo"gtltXgt10lt/
XgtltYgt20lt/Ygtlt/Pointgt') -- this insert fails,
value foo is not a dim (integer) INSERT INTO
point_tab VALUES( 'ltPoint xmlns"urngeo"gtltXgt10lt/
XgtltYgtfoolt/Ygtlt/Pointgt')
9
XML indexes

• You can create XML INDEXes on an XML column
• optimizes XML Queries on the column
• primary XML index must be created first (node
  table)
• three specialized index types also available
• VALUE optimizes content queries
• PATH optimizes structure queries
• PROPERTY optimizes name/value pair predicates

CREATE TABLE xml_tab ( id integer primary key,
doc xml) GO CREATE PRIMARY XML INDEX xml_idx
on xml_tab (doc) GO
10
Enhancements to SELECT...FOR XML

• SELECT FOR XML is an extension to Transact-SQL
• usually produces a stream
• in SQL Server 2005, it can also produce an XML
  data type
• use TYPE keyword after FOR XML
• can be used with RAW, AUTO formats
• can be used to query a "collection" of documents

DECLARE _at_x xml SET _at_x SELECT FROM authors
FOR XML AUTO, TYPE
11
FOR XML PATH

• SELECT FOR XML PATH allows shaping of output
• FOR XML AUTO, RAW allow little shaping
• FOR XML EXPLICIT is complex to write
• FOR XML PATH uses path-syntax in column aliases
• allows "creation" of elements
• simpler than XML EXPLICIT
• can specify namespaces with XMLNAMESPACES function

12
XML PATH example
WITH XMLNAMESPACES('urnauthors' AS au) SELECT
au_id as _at_auauthorid, au_fname as
name/firstname, au_lname as
name/lastname FROM authors FOR XML PATH
lt! one row per selected row --gt ltrow
xmlnsau"urnauthors" auauthorid"111-11-1
111"gt ltnamegt ltfirstnamegtBoblt/firstnamegt
ltlastnamegtSmithlt/lastnamegt lt/namegt lt/rowgt ...
13
Producing an XML Schema with FOR XML

• XMLSCHEMA keyword prepends XML Schema to FOR XML
• can be used with other keywords
• schema namespace can be specified

DECLARE _at_x xml SET _at_x (SELECT FROM authors
FOR XML AUTO, TYPE, XMLSCHEMA('urnauthors')
14
More FOR XML Enhancements

• Many refinements and extensions to FOR XML
• element-centric XML using FOR XML RAW
• generate xsinil for NULL database values
• FOR XML AUTO/RAW, ELEMENTS XSINIL
• nested FOR XML queries
• can specify ROOT element
• can name ltrowgt element in FOR XML RAW
• changes to nesting algorithm in XML AUTO
• supports new data types
• varchar(max), nvarchar(max), varbinary(max)
• UDTs (must be cast/converted to XML in query)

15
Using XML Data Type with OpenXml

• OpenXml function can use XML data type
• originally used varchar or TEXT input
• still must be parsed with sp_xml_preparedocument
• permits user-defined fill into existing tables
• overflow column may be xml

-- this assumes that the document -- looks
exactly like the table CREATE PROCEDURE
fillTablesWithMyDoc(_at_doc xml) AS declare _at_idoc
int exec sp_xml_preparedocument _at_idoc OUTPUT,
_at_doc SELECT FROM OpenXML(_at_idoc, '/ROOT',2)
WITH (mytable) AS A INTO mytable exec
sp_xml_removedocument _at_idoc
16
Inserting XML Through Bulk Load

• New BULK Rowset Provider can insert XML
• inserts from file to XML column
• using SINGLE_BLOB option inserts only XML column
• SINGLE_CLOB, SINGLE_NCLOB also work
• SINGLE_BLOB avoids encoding problems
• format file required if inserting more than one
  row

CREATE TABLE invoices ( rowid int primary key
identity, invoice xml ) INSERT invoices
SELECT FROM OPENROWSET (BULK
'c\invoice.txt', SINGLE_BLOB) as X
17
XML data type and FOR XML enhancements
18
SQL Server 2005 Support of XQuery

• XQuery is supported through methods on the XML
  type
• xml.exist - returns bool
• xml.value - returns scalar
• xml.query - returns XML data type instance
• xml.nodes - returns one column rowset w/XML
  column
• xml.modify - modifies an instance
• These methods can return
• XML data type (query) or SQL Scalar (exist,
  value)
• columns in rowsets - when used with SQL SELECT
• variables

19
XQuery Expressions

• XQuery is a superset of XPath
• can use XPath or FLWOR expressions
• almost all valid XPath statements are also XQuery
  statements
• XPath used to select values in FLWOR expressions

for p in /people/person where p/age gt 30 order
by p/age1 return p/name
20
FLWOR Expressions

• XML Queries are FLWOR expressions
• made up of five types of clause
• FOR
• LET (not supported by SQL Server 2005)
• WHERE
• ORDER BY
• RETURN

21
Constructors

• Constructors are used to construct nodes
• constructors can construct static content XML
  1.0 Namespaces serialized form
• constructors can construct dynamic content
• dynamic content can be content of elements and/or
  attributes
• dynamic part of constructor enclosed in curly
  braces
• or use literal constructor

22
Dynamic constructors Text
return ltpersongtltnamegt p/name1/givenNam
e1/text() lt/namegtlt/persongt
ltpersongt
ltnamegtMartinlt/namegt
lt/persongt
ltpersongt
ltnamegtSimonlt/namegt
lt/persongt
23
Literal constructors
return element person attribute name
data(p/name1/givenName1/tex
t()1)
ltperson name "Martin"gt
ltperson name "Simon"gt
24
value and exist XML data type methods

• value() data type method accesses scalar value in
  xml column
• XQuery or simple XPath can be used
• exist() data type method tests conditions in xml
  column
• XQuery or simple XPath can be used
• used with column name separated by "."

select invoice.value('1/_at_ID','int')
from Invoices
XPath expression gets
value of _at_ID attribute
ltInvoice ID"12"gt
12
...
34
lt/Invoicegt
Invoices table
26
invoice column
25
xml.exist

• xml.exist uses XML type and XQuery expression
• returns false if query returns NULL sequence
• returns true otherwise
• Usable in XML type check constraints
• xml functions must be encapsulated as UDF

26
xml.value

• xml.value return a SQL Server scalar type
• SQL type, xml data type instance, XQuery as input
• returns scalar type or NULL
• cannot return XML type instance
• Usable within SQL Server
• in predicates
• as result values

27
Using xml.value
-- insert some rows INSERT xml_tab
VALUES('ltpeoplegtltperson name"curly"/gtlt/peoplegt')
INSERT xml_tab VALUES('ltpeoplegtltperson
name"larry"/gtlt/peoplegt') INSERT xml_tab
VALUES('ltpeoplegtltperson name"moe"/gtlt/peoplegt')
-- this query SELECT id, xml_col.value('/people/
person/_at_name','varchar(50)') AS name FROM xml_tab
-- yields this resultset id
name -------------------------- 1 curly 2
larry 3 moe
28
xml.query

• xml.query returns an XML data type instance
• XML type instance and XQuery are inputs
• can return scalar type as XML fragment
• can use constructors to compose new data

from invoice column in Invoices table
ltInvoicegt
ltLineItemgtSodalt/LineItemgt
ltLineItemgtIcelt/LineItemgt
lt/Invoicegt
SELECT invoice.query('Invoice/LineItem')
selects LineItem's
FROM Invoices
from XML data
-- yields this resultset ------------------------
-- ltLineItemgtSodalt/LineItemgtltLineItemgtIcelt/LineIte
mgt
ltLineItemgtSodalt/LineItemgtltLineItemgtIcelt/LineItemgt
29
Using Relational Data in XQuery

• SQL Server XQuery can combine relational and XML
• sqlvariable - uses TSQL variables
• sqlcolumn - uses column value
• same row as XML type column

30
Using Relational Data With XML
-- returns ltligtmoe in record number xlt/ligt --
where x is the ID column, or blank column SELECT
xml_col.query(' for b in //person where
b/_at_name"moe" return ltligt data(b/_at_name)
in record number
sqlcolumn("xml_tab.id")lt/ligt ') FROM xml_tab
-- returns ltligtmoe is a stoogelt/ligt DECLARE
_at_occupation VARCHAR(50) SET _at_occupation ' is a
stooge' SELECT xml_col.query(' for b in
//person where b/_at_name"moe" return
ltligt data(b/_at_name) sqlvariable("_at_occupa
tion") lt/ligt ') FROM xml_tab
31
nodes method

• nodes can decompose an XML data type
• similar to value(), but produces
• references to XML nodes
• many nodes rather than single scalar value
• result is one-column rowset
• T-SQL CROSS APPLY can be use to produce rowset
• similar to OpenXML
• better performance, XML column is already parsed

-- xmlinvoices table contains custid, xmlinv
columns -- return one row for each
lineitem SELECT custid, tab.col.query('itemno')
--get itemno subelement FROM xmlinvoices
CROSS APPLY xmlinv.nodes('/invoice/lineitem') as
tab(col) WHERE custid 'ALFKI'
32
XQuery DML - Using xml.modify
-- use modify to insert a subelement SET
_at_x.modify( 'insert ltInvoiceDategt2002-06-15lt/Invo
iceDategt into /Invoice1 ') -- or insert an
attribute SET _at_x.modify('insert attribute
status"backorder" into /Invoice1 ') --
this deletes all LineItem elements SET
_at_x.modify('delete /Invoice/LineItems/LineItem') -
- change the value of the CustomerName
element SET _at_x.modify('replace value of
/Invoice1/CustomerName1/text()1 with
"John Smith" ')
33
XQuery
34
Why SQL Server and Web Services?

• Databases can deal with Web service clients
  directly
• Using TDS limits potential clients
• Web service check box requirement
• OLE DB, ODBC, .NET data provider required
• JDBC driver required
• free TDS - reduced functionality
• Web services from SQL Server
• HTTP, all clients have it
• XML, all clients support it

35
SQL Server Web Services descriptions

• SQL Server Web services configured in database
• just like other database features
• SQL Server Web service operations part of
  database
• assemblies, sprocs in database
• just like other database functionality
• SQL Server Web service security part of data base
• just like other database security
• SQL Server Web services fully described inside
  database
• just as database objects should be

36
SQL Server directly leverages HTTP

• Uses HTTP kernel protocol stack
• less layers, better scalability and thru-put
• requires WS2K3 or XP SP2
• Can use SSL
• Inetinfo.exe not used

37
Web Service configuration

• Web service endpoint must be configured
• location, access, and security
• Web service operations must be configured
• specific operation names and/or ad hoc sql
  batches
• CREATE ENDPOINT for HTTP used to configure
• names endpoint
• before FOR SOAP configures endpoint
• after FOR SOAP configures soap operations

38
Endpoint authentication

• Basic authentication supported (w/Windows login)
• WS-Security supported w/SQL login (WS-I standard)
• but must specify an endpoint authentication type
  too
• Digest and integrated authentication supported
• integrated requires client in same or trusted
  domain
• Multiple authentication types supported
• Client must also be granted CONNECT ENDPOINT
  privilege
• Clear_port and ssl_port may be specified

39
Endpoint startup

• Endpoint must be in started state to accept
  request
• initial state can be started, stopped, disabled
• disabled state requires SQL Server restart to
  enable
• Endpoint state changed by using ALTER HTTP
  ENDPOINT

CREATE ENDPOINT generalLedger STATE STARTED AS
HTTP ( ... ) FOR SOAP ( ... )
endpoint will accept requests
CREATE ENDPOINT generalLedger STATE STOPPED AS
HTTP ( ... ) FOR SOAP ( ... )
endpoint will not accept requests
40
Operation configuration

• Operations supported must be configured
• operation name, function, schema
• defined after FOR SOAP
• Context for Web methods must be configured
• namespace, database used, etc.

41
Endpoint operation

• Webmethod is used to define an endpoint operation
• namespace and name of operation
• name of stored proc or udf that implements it
• standard schema, none or default
• Multiple operations may be defined

42
Endpoint context

• Context operations execute in must be defined
• automatic WSDL generation
• ad hoc queries, disabled by default
• database used, default for login if not specified
• namespace for endpoint

FOR SOAP ( WEBMETHOD ..., WSDL
DEFAULT, BATCHES ENABLED, DATABASE
'pubs', NAMESPACE 'urnAccounting' )
43
Typical Web Service configuration
CREATE ENDPOINT Accounting STATE STARTED AS
HTTP ( SITE 'www.account.com', PATH
'/nwind', AUTHENTICATION (INTEGRATED),
PORTS (CLEAR) FOR SOAP ( WEBMETHOD
'http//tempUri.org/'.'GetCustomerInfo'
(name'Northwind.dbo.GetCustInfo',
schemaSTANDARD ), WSDL DEFAULT,
BATCHES ENABLED, DATABASE 'northwind',
NAMESPACE 'http//Northwind/Customers' ) GO
44
Creating and calling a SQL Server 2005 web service
45
.NET Client to Retrieve Results
static void Main(string args) //
instantiate the proxy class myprog.localhost.soa
p s new myprog.localhost.soap() // use
security of current client principal
s.Credentials System.Net.CredentialCache.Default
Credentials object oa try oa
s.AuthorsByRoyaltyAsDataSets(50)
processResults(oa) catch (Exception e)
Console.WriteLine("0 1",
e.GetType().ToString(), e.Message) if (oa)
Console.WriteLine("error, 0 results",
oa.Length)
46
Processing Results
void processResults(object oa) for (int
i0iltoa.Lengthi) switch
(oai.GetType().ToString()) case
"System.Data.DataSet" DataSet ds
(DataSet)oai Console.WriteLine(ds.Ge
tXml()) break case "myprog.localhost.Sql
Message" SqlMessage sqlm
(SqlMessage)oai Console.WriteLine("Er
ror 0 in proc 1", sqlm.Message,
sqlm.Procedure) break case "System.Xml.XmlElem
ent" XmlElement elem
(XmlElement)oai
Console.WriteLine(elem.OuterXml) break
// more cases... default
Console.WriteLine("Unexpected type 01",
oai.GetType().ToString(),
oai.ToString()) break