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 ( @x XML, @y XML OUTPUT) AS ... CREATE FUNCTION simple ( @x NVARCHAR(max)) RETURNS XML AS DECLARE @a XML SET @a = @x ... RETURN @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 '<xs:schema ... targetNamespace targetNamespace = urn:geo> ... </xs:schema>' 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 'urn:geo' namespace thepoint xml(CONTENT geocoll) GO -- this works, schema-valid Point INSERT INTO point_tab VALUES( '<Point xmlns="urn:geo"><X>10</X><Y>20</Y></Point>') -- this insert fails, value foo is not a dim (integer) INSERT INTO point_tab VALUES( '<Point xmlns="urn:geo"><X>10</X><Y>foo</Y></Point>')

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 @x xml SET @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('urn:authors' AS au) SELECT au_id as [@au:authorid], au_fname as [name/firstname], au_lname as [name/lastname] FROM authors FOR XML PATH <!– one row per selected row --> <row xmlns:au="urn:authors" au:authorid="111-11-1111"> <name> <firstname>Bob</firstname> <lastname>Smith</lastname> </name> </row> ...

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 @x xml SET @x = (SELECT * FROM authors FOR XML AUTO, TYPE, XMLSCHEMA('urn:authors')

14. More FOR XML Enhancements • Many refinements and extensions to FOR XML • element-centric XML using FOR XML RAW • generate xsi:nil for NULL database values • FOR XML AUTO/RAW, ELEMENTS XSINIL • nested FOR XML queries • can specify ROOT element • can name <row> 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(@doc xml) AS declare @idoc int exec sp_xml_preparedocument @idoc OUTPUT, @doc SELECT * FROM OpenXML(@idoc, '/ROOT',2) WITH (mytable) AS A INTO mytable exec sp_xml_removedocument @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 > 30 order by $p/age[1] 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 <person><name> {$p/name[1]/givenName[1]/text()} </name></person> <person> <name>Martin</name> </person> <person> <name>Simon</name> </person>

23. Literal constructors return element person { attribute name {data($p/name[1]/givenName[1]/text()[1])} } <person name = "Martin"> <person name = "Simon">

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]/@ID','int') from Invoices XPath expression gets value of @ID attribute <Invoice ID="12"> 12 ... 34 </Invoice> 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('<people><person name="curly"/></people>') INSERT xml_tab VALUES('<people><person name="larry"/></people>') INSERT xml_tab VALUES('<people><person name="moe"/></people>') -- this query SELECT id, xml_col.value('/people/person/@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 <Invoice> <LineItem>Soda</LineItem> <LineItem>Ice</LineItem> </Invoice> selects LineItem's SELECT invoice.query('Invoice/LineItem') FROM Invoices from XML data -- yields this resultset -------------------------- <LineItem>Soda</LineItem><LineItem>Ice</LineItem> <LineItem>Soda</LineItem><LineItem>Ice</LineItem>

29. Using Relational Data in XQuery • SQL Server XQuery can combine relational and XML • sql:variable - uses TSQL variables • sql:column - uses column value • same row as XML type column

30. Using Relational Data With XML -- returns <li>moe in record number x</li> -- where x is the ID column, or blank column SELECT xml_col.query(' for $b in //person where $b/@name="moe" return <li>{ data($b/@name) } in record number {sql:column("xml_tab.id")}</li> ') FROM xml_tab -- returns <li>moe is a stooge</li> DECLARE @occupation VARCHAR(50) SET @occupation = ' is a stooge' SELECT xml_col.query(' for $b in //person where $b/@name="moe" return <li>{ data($b/@name) } {sql:variable("@occupation") }</li> ') 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 @x.modify( 'insert <InvoiceDate>2002-06-15</InvoiceDate> into /Invoice[1] ') -- or insert an attribute SET @x.modify('insert attribute status{"backorder"} into /Invoice[1] ') -- this deletes all LineItem elements SET @x.modify('delete /Invoice/LineItems/LineItem') -- change the value of the CustomerName element SET @x.modify('replace value of /Invoice[1]/CustomerName[1]/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. endpoint name CREATE ENDPOINT genled ... AS HTTP (...) endpoint configuration FOR SOAP (...) operations configuration 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. CREATE ENDPOINT genLed... AS HTTP (Authentication = (INTEGRATED), PORTS = (CLEAR), CREATE ENDPOINT genLed... AS HTTP CLEAR_PORT = 8082 (Authentication = (BASIC), ...) PORTS = (SSL) FOR SOAP ... ) FOR SOAP 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. CREATE ENDPOINT genLed STATE = STARTED AS HTTP ( ... ) FOR SOAP ( operations supported WEBMETHOD a (...), by endpoint WEBMETHOD b (...), ... ) context for operations 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. client sees a CustomerInfo operation FOR SOAP operation is implemented WEBMETHOD by the GetCInfo sproc 'http://tempUri.org/'.' CustomerInfo ' in the pubs database (name='Pubs.dbo.GetCInfo', schema=STANDARD ), schema definition is WEBMETHOD ... , available for this operation 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 = 'urn:Accounting' )

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', schema=STANDARD ), 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.soap s = new myprog.localhost.soap(); // use security of current client principal s.Credentials = System.Net.CredentialCache.DefaultCredentials; 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 i=0;i<oa.Length;i++) { switch (oa[i].GetType().ToString()) { case "System.Data.DataSet": DataSet ds = (DataSet)oa[i]; Console.WriteLine(ds.GetXml()); break; case "myprog.localhost.SqlMessage": SqlMessage sqlm = (SqlMessage)oa[i]; Console.WriteLine("Error: {0} in proc {1}", sqlm.Message, sqlm.Procedure); break; case "System.Xml.XmlElement": XmlElement elem = (XmlElement)oa[i]; Console.WriteLine(elem.OuterXml); break; // more cases... default: Console.WriteLine("Unexpected type {0}:{1}", oa[i].GetType().ToString(), oa[i].ToString()); break; } } }