SQLServerCentral.com Articles tagged XML

Using XML from data variable option in SSIS

Wed, 15 May 2013 06:00:00 UT

Learn how you can manipulat XML documents solely in memory. No file required.

Ad-Hoc XML File Querying

Thu, 28 Mar 2013 06:00:00 UT

When you need to shred just part of the data within a large XML file into a SQL Server table, the most efficient way is to just select what you need via XQuery or by using XPath, before shredding it into a table. But precisely how would you do that?

Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

Shred XML column using SSIS

Wed, 27 Feb 2013 07:00:00 UT

This article describes a way using SSIS to shred a XML column from a source table into its respective columns in the destination table.

Stairway to XML: Level 10 - Converting XML Data

Wed, 13 Feb 2013 07:00:00 UT

In this Level, we look at how to convert string values to XML and how to convert XML to character types.

Precision Indexing: Basics of Selective XML Indexes in SQL Server 2012

Wed, 06 Feb 2013 07:00:00 UT

Seldom has a SQL Server Service pack had such an effect on database development as when SQL Server 2012 SP1 introduced selective XML indexes. These transform the practicality of querying large amounts of XML data. Seth Delconte demonstrates how and why this feature makes all the difference.

Stairway to XML: Level 9 - Creating XML-Based Functions

Wed, 30 Jan 2013 07:00:00 UT

In this Level, we’ll look at how to use XML methods within user-defined functions to return XML fragments and values from your target XML instance.

Stairway to XML: Level 1 - Introduction to XML

Wed, 09 Jan 2013 07:00:00 UT

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

Stairway to XML: Level 8 - Deleting Data from an XML Instance

Mon, 05 Nov 2012 07:00:00 UT

in order to use the modify() method to delete data from typed and untyped XML instances, you must pass an XML DML expression as an argument to the method. That expression must include the delete keyword, along with an XQuery expression that defines the XML component to be deleted. Robert makes it seem simple, as usual.

Manipulating XML Data in SQL Server

Thu, 01 Nov 2012 06:00:00 UT

When the average database developer is obliged to manipulate XML, either shredding it into relational format, or creating it from SQL, it is often done 'at arms length'. A shame, since effective use of techniques that go beyond the basics can save much code,

Stairway to XML: Level 7 - Updating Data in an XML Instance

Mon, 29 Oct 2012 06:00:00 UT

You need to provide the necessary keywords and define the XQuery and value expressions in your XML DML expression in order to use the modify() method to update element and attribute values in either typed or untyped XML instances in an XML column. Robert Sheldon explains how.

Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here

Stairway to XML: Level 6 - Inserting Data into an XML Instance

Wed, 17 Oct 2012 06:00:00 UT

The modify method lets you manipulate XML data using XML DML. It can insert, alter or delete data. In this level, Robert shows how to use the method to insert a node into an XML instance.

Stairway to XML: Level 5 - The XML exist() and nodes() Methods

Mon, 15 Oct 2012 06:00:00 UT

The XML exist() method is used, often in a WHERE clause, to check the existence of an element within an XML document or fragment. The nodes() method lets you shred an XML instance and return the information as relational data.

Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.

Stairway to XML: Level 4 - Querying XML Data

Mon, 08 Oct 2012 06:00:00 UT

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.

Stairway to XML: Level 3 - Working with Typed XML

Mon, 01 Oct 2012 06:00:00 UT

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.

Stairway to XML: Level 2 - The XML Data Type

Wed, 26 Sep 2012 06:00:00 UT

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

New! Safeguard your SQL backups
Protect your backups from onsite disaster with SQL Backup Pro and a Hosted Storage account from Red Gate. Learn more

Stairway to XML: Level 1 - Introduction to XML

Wed, 09 Jan 2013 07:00:00 UT

Learn XML

Thu, 03 May 2012 06:00:00 UT

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

Basics of XML and SQL Server, Part 5: Generate/Transmit XML with SSIS

Wed, 04 Apr 2012 06:00:00 UT

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

Basics of XML and SQL Server, Part 4: Create an XML invoice with SSIS

Wed, 28 Mar 2012 06:00:00 UT

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.

Basics of XML and SQL Server, Part 3: Transform and Shred XML in SSIS

Wed, 21 Mar 2012 06:00:00 UT

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.

Basics of XML and SQL Server, Part 2: Shredding XML

Wed, 14 Mar 2012 06:00:00 UT

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.

Basics of XML and SQL Server, Part 1: Streaming XML

Wed, 07 Mar 2012 07:00:00 UT

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.

Converting String Data to XML and XML to String Data

Tue, 07 Feb 2012 07:00:00 UT

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.

Using XML to pass lists as parameters in SQL Server

Thu, 26 Jan 2012 07:00:00 UT

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.

Incorporating XML into your Database Objects

Fri, 20 Jan 2012 07:00:00 UT

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.

XML-XSL Transformations in SQL Server

Wed, 04 Jan 2012 07:00:00 UT

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

The XML Methods in SQL Server

Tue, 03 Jan 2012 07:00:00 UT

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.

Managing Data Dictionaries in DW and DSS Database Applications

Tue, 20 Dec 2011 07:00:00 UT

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

Working with the XML Data Type in SQL Server

Fri, 02 Dec 2011 07:00:00 UT

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.

XML Configuration files in SQL Server Integration Services

Thu, 25 Aug 2011 06:00:00 UT

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.