Post-it notes: Chapter 11 – SQL Server XML Help

April 8, 2013

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

 XML XQuery

XML Indexing

  • Index XML data type – BLOB (up to 2GB)
  • Used to optimise ‘xquery’ queries
  • Indexes all tags, values and paths
  • Two types of indexes:
    • Primary: Create primary XML index
    • Secondary: Create XML index (which is secondary)
  • The first index on XML data type must be PRIMARY
  • To create XML index, table must have a clustered index on Primary Key
  • CREATE PRIMARY XML  INDEX (xx) on XML_TABLE (custaddr)
  • CREATE XML INDEX USING [primary index] FOR [path|Value|property]
    • PATH – helps queries use xml path expression
    • VALUE – helps queries search values in xml
    • PROPERTY – helps queries retrieve particular property
  • ALTER INDEX – modify existing XML indexes
  • Indexes are enabled by default. Use ALTER to disable
  • DROP INDEX, if primary it drops secondary’s
  • Cannot create XML index on
    • View
    • Table variabl (xml column)
    • Table variable (xml type)

HTTP Endpoints

  • Not supported in Express version
  • Expose stored procedures and functions as methods to be called by SOAP
  • SQL Server listens for HTTP requests natively
  • CREATE ENDPOINT AS [HTTP|TCP] for [SOAP|TSQL|SERVICE BROKER|DB MIRRORING]
  • [HTTP|TCP] – known as protocols
  • [SOAP|TSQL|SERVICE BROKER|DB MIRRORING]  – payloads
  • Native XML Web services (SOAP/HTTP endpoints) have been depreciated in SQL 2008
  • CREATE ENDPOINT – the state can be  started/stopped/disabled
    • Started – start and listening
    • Stopped – Stopped but Listening and returns errors
    • Disabled – Does not listen
    • DDL – CREATE ENDPOINT/ALTER ENDPOINT/ DROP ENDPOINT

Securing a User EndPoint

  • Connect permissions to Endpoint
  • Global permissions to SQL Server
  • Permissions to default system endpoints granted to all logins
  • To restrict access, deny everyone (deny connect) and then grant connect
  • To restore to default, grant connect to public group

Testing the EndPoint

Web Services

  • Web Service is – Platform independent message framework
  • Accessed by SOAP/UDDI/WDSL – consumes – Web service (xml)
  • Based on established HTTP protocol

XQuery Explained

  • Language used to query XML data
  • SQL 2008 supports subset of xquery
  • Based on XPath
  • XQuery used to query XML Docs/Data Sources
  • 4 Methods involved:
    • Query() – returns xml data that matches query. Select emp.query(..) from ABC
    • Value () – Extract a value from XML doc. Allows comparison with non-xml columns. Select emp.value(…) from ABC
    • Exist() – Check existence of value (returns 1 or 0). Select xxx from yyy where emp.exists(…)
    • Nodes() – return rowset for each row. Useful for making XML into relational. Cross apply emp.nodes(…)
  • XQuery syntax
    • Case sensitive
    • Valid XML names
    • Variables use $
  • SQL 2008 provides extension which allow XQuery to modify data – [UPDATE xxx SET emp.modify(insert/delete/replace)]
    • Insert – insert xml before/after existing. Insert attributes
    • Delete – delete elements and attributes
    • Replace Value Of – replace node, must be single user mode

XPath

  • Standard navigation language
  • Used to identify set of nodes in xml
  • Implemented in SQL 2008, subset of W3C xpath specs (SQLXML 4.0)
  • Used to compute values from XML to DOC
  • Number of XPATH expressions types – google it.

 

Thanks for reading.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: