XML DATA and XQUERY IN SQL SERVER
SQL Server provides a powerful platform for developing rich applications for semi-structured data management. Support of XML is integrated into all the components of SQL SERVER which includes XML data type, ability to specify XQuery on XML data, managing bulk loading of XML data, retrieving relational data in XML format(FOR XML), retrieving XML data in relational format(OPENXML) etc. This session will be beneficial for people with basic knowledge of SQL SERVER.
- XML Data and XQUERY in SQL SERVER Presenter:Bishwaranjan Sandhu, Mindfire Solutions Date: 28/07/2014
- Presenter: Bishwaranjan Sandhu, Mindfire Solutions AGENDA -> Basic Syntax and use of of XML data type -> Basic Syntax of the FOR XML Clause -> Use RAW Mode with FOR XML -> Use AUTO Mode with FOR XML -> Use PATH Mode with FOR XML -> OPENXML -> XQuery on XML data -> Examples
- Basic Syntax and use of of XML data type Relational or XML Data Model Relational data for structured data with schema.XML is a good choice if you want a platform-independent model in order to ensure portability of the data by using structural and semantic markup. Reasons for Storing XML Data in SQL Server You want to share, query, and modify your XML data in an efficient and transacted way. You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer. XML Storage Options Limitations of the xml Data Type Presenter: Bishwaranjan Sandhu, Mindfire Solutions
- Basic Syntax of the FOR XML Clause Arguments RAW[('ElementName')] AUTO EXPLICIT PATH XMLDATA ELEMENTS BINARY BASE64 TYPE ROOT [('RootName')] Presenter:Bishwaranjan Sandhu, Mindfire Solutions
- Presenter:Bishwaranjan Sandhu , Mindfire Solutions Use RAW Mode with FOR XML RAW mode transforms each row in the query result set into an XML element that has the generic identifier , or the optionally provided element name. Example: Retrieving Product Model Information as XML Example: Specifying XSINIL with the ELEMENTS Directive Example: Requesting Schemas as Results with the XMLDATA and XMLSCHEMA Options Example: Retrieving Binary Data Example: Renaming the Element Example: Specifying a Root Element for the XML Generated by FOR XML
- Use AUTO Mode with FOR XML Presenter: Bishwaranjan Sandhu, Mindfire Solutions AUTO mode returns query results as nested XML elements. This does not provide much control over the shape of the XML generated from a query result. The AUTO mode queries are useful if you want to generate simple hierarchies. However, Use EXPLICIT Mode with FOR XML and Use PATH Mode with FOR XML provide more control and flexibility in deciding the shape of the XML from a query result. Each table in the FROM clause, from which at least one column is listed in the SELECT clause, is represented as an XML element. The columns listed in the SELECT clause are mapped to attributes or subelements, if the optional ELEMENTS option is specified in the FOR XML clause.
- Use PATH Mode with FOR XML Constructing XML Using FOR XML, the PATH mode provides a simpler way to mix elements and attributes. PATH mode is also a simpler way to introduce additional nesting for representing complex properties. You can use FOR XML EXPLICIT mode queries to construct such XML from a rowset, but the PATH mode provides a simpler alternative to the potentially cumbersome EXPLICIT mode queries Presenter: Bishwaranjan Sandhu, Mindfire Solutions Columns without a Name Columns with a Name Columns with the Name of an XPath Node Test Columns that Contain a Null Value By Default Examples: Using PATH Mode
- OPENXML Presenter: Bishwaranjan Sandhu, Mindfire Solutions OPENXML, a Transact-SQL keyword, provides a rowset over in-memory XML documents that is similar to a table or a view. OPENXML allows access to XML data as though it is a relational rowset. It does this by providing a rowset view of the internal representation of an XML document. The records in the rowset can be stored in database tables. OPENXML Parameters An XML document handle (idoc) An XPath expression to identify the nodes to be mapped to rows (rowpattern) A description of the rowset to be generated Mapping between the rowset columns and the XML nodes
- Examples Demo... Presenter: Bishwaranjan Sandhu, Mindfire Solutions
- Presenter:Bishwaranjan Sandhu, Mindfire Solutions Thank you
- www.mindfiresolutions.com https://www.facebook.com/MindfireSolutions http://www.linkedin.com/company/mindfire-solutions http://twitter.com/mindfires