You can extract a subset of data from an XML instance by using the query() method, and you can use the value() method to retrieve individual element and attribute values from an XML instance.  Read more...

You can enforce the validation of an XML data type, variable or column by associating it with an XML Schema Collection. SQL Server validates a typed XML value against the rules defined in the schema collection so that INSERT or UPDATE operations will succeed only if the value being inserted or updated is valid as per the rules defined in the Schema Collection.  Read more...

Robert Sheldon describes SQL Server's XML Data Type, and shows that it is as easy to configure a variable, column, or parameter with the XML data type as configuring one of these objects with any other datatype  Read more...

In this level, Rob Sheldon explains what XML is, and describes the components of an XML document, Elements and Attributes. He explains the basics of tags, entity references, enclosed text, comments and declarations  Read more...

Steve Jones talks about XML and how it will be more and more important for DBAs to understand this in the future.  Read more...

This SSIS package generates XML invoice documents from data stored in SQL Server, then FTPs and/or Emails them to clients.  Read more...

This article demonstrates how to build an SSIS package that generates an XML invoice document from data stored in SQL Server and saves it to an XML file.  Read more...

This SSIS package performs multiple XSL transformations on an XML document, then shreds the transformed document and inserts its data into a SQL Server table.  Read more...

In Part 2 of this series we will shred an XML document and insert its records into a SQL Server table using both the OpenXML and XQuery methods.  Read more...

This article demonstrates how to generate an XML stream from SQL Server using the 'FOR XML PATH' statement, and how to explore that XML stream interactively using an XSL transformation.  Read more...

In general XML documents or fragments are held in strings as text markup. In SQL Server, XML variables and columns are instead tokenised to allow rapid access to the data within. This is fine, but can cause some odd problems, such as ' entitization'. What do you do if you need to preserve the formatting? As usual Rob Sheldon comes to our aid.   Read more...

Every so often, the question comes up on forums of how to pass a list as a parameter to a SQL procedure or function. Phil Factor provides some examples using XML, and tests them against other popular methods.  Read more...

XML data can become a full participant in a SQL Server Database, and can be used in views, functions, check constraints, computed columns and defaults. Views and table-valued functions can be used to provide a tabular view of XML data that can be used in SQL Expressions. Robert Sheldon explains how.  Read more...

With the release of Master Data Services (MDS) for SQL Server 2008R2, it is now possible to perform XML transformations on the database server.  Read more...

The XML Data type has definite uses, but the way of interrogating, retrieving, and manipulating the values of properties and attributes within XML have been so foreign to the SQL language as to be somewhat of a barrier to their use. Fortunately, Robert Sheldon has once more managed to make the subject accessible to those of us who just need to get the job done.  Read more...

Second in the series of tools & techniques to help manage aspects Of Enterprise BI implementation.  Read more...

The XML data type, introduced in SQL Server 2005, is a powerful construct. When used wisely, it can provide useful extensions to SQL Server. Robert Sheldon, in the first part of a series, describes how create and index a XML column in a table, and discusses when you should consider using an XML data type.  Read more...

Package configuration files are a great way of providing the values of SSIS package properties so that packages can be used in a far more versatile way. They make the deployment of SSIS packages easier and can provide parameters that are based on the server configuration, or which change for each runtime. They're easy to understand, especially when explained by Rob Sheldon.   Read more...

Unstructured and XML semi-structured data is now used more than structured data. But fixed structured data still keeps businesses running day in and day out, which requires consistent predictable highly principled processing for correct results. For this reason, it would be very useful to have a general purpose SQL peer-to-peer collaboration capability that can utilize highly principled hierarchical data processing and its flexible and advanced structured processing to support dynamically structured data and its dynamic structured processing. This flexible dynamic structured processing can change the structure of the data as necessary for the required processing while preserving the relational and hierarchical data principles. This processing will perform freely across remote unrelated peer locations anytime and transparently process unpredictable and unknown structured data and data type changes automatically for immediate processing using automatic metadata maintenance.  Read more...

The SQL-92 standard introduced the LEFT Outer Join operation which offers a powerful alternative to standard relational processing to perform full hierarchical processing naturally and inherently. This enables SQL to transparently integrate relational data with XML, IMS and other forms of legacy hierarchical data. This can also allow the so far overlooked integration of IMS and legacy data with the Internet and XML, and visa versa.   Read more...