Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
SQL Server I/O: XML in Database Terms
Last updated Mar 28, 2003.
As a database professional, periodically you'll be asked to step outside the comfort of SQL Server knowledge and into a more data-centric view. You need to be familiar with many data technologies, especially the ones to which SQL Server can talk natively.
SQL Server has many ways to output and receive data. The most common way is in a data stream to an application, but you can also send sand receive data to a text file, an Excel file, Oracle tables, a backup, and also XML. Each has a use, and you should be conversant with them all.
In this series of articles, I explain methods, other than programming, that get data into and out of a SQL Server database. Here, I start with XML.
I covered XML briefly in an article on SQL Server features, which serves as a brief overview of how SQL Server uses XML. In the next few articles, I'll dive down a bit deeper on the topics introduced in that earlier reference. In addition, the references section at the end of this article points out some great XML resources right here on InformIT.
Let's begin this exploration with the database reasons for XML. By now, you've probably heard a lot about this file format, which is all XML really is. There are many reasons to store data in a format other than SQL Server, but the most compelling I've seen is data interchange.
The beauty of XML is that it is self-defining, and it allows you a great deal of flexibility to create custom tags. In addition, many standard tags have been adopted by disciplines as varied as mathematics and insurance claims. I work in a large company, and we often have to exchange data with client and vendor systems. Some of these systems are connected with Business-to-Business (B2B) software, but some are smaller or more complicated hookups; XML transfers have been a real help in those situations.
To properly frame this discussion, let's define a few terms. By now you've probably read at least a few articles describing XML layouts. I won't repeat that information here. What may be more useful for our purposes is to put a few definitions into database terms. Before I do that, we need to get the ground rules straight for making a comparison like this.
XML is a specification, but it lends itself to creating hierarchical data arrangements. SQL Server stores data using relational concepts, so the two don't meld together seamlessly. There are things you can do in XML that you can't in a SQL Server database, and vice-versa. So take the following information in that spirit. I'll refer back to this information fairly often throughout this series.
XML Term: Document
An XML document is a set of ASCII characters with two requirements: A special set of characters called a declaration, and an open and closed root tag. The document can be this simple:
<?xml version="1.0" ?> <authors> </authors>
Note that the above example is the same as:
<?xml version="1.0" ?> <authors> </authors>
In the database world, the corollary to the XML document is a table. You could actually have several tables within an XML document, but that makes structuring the document to act like a relational database structure more difficult.
XML Term: Processing Instructions
Processing Instructions are commands to the XML processing engine, or consumer. Most Web browsers have an XML processor, as does SQL Server and other tools.
Processing Instructions start and end this way: <? ?>. There are several, but the one that is required is the declaration:
<?xml version="1.0" ?>
The database corollary for a directive might be the file storage format.
XML Term: Element (Tag)
Elements in XML are the delimiters of data. Any word can be used as a tag, and case matters. Once a tag is declared, it must be terminated with a slash (/) to parse the document correctly. Although HTML is fairly forgiving about un-closed tags and case, XML is not.
Element tags are analogous to a database column. You can structure the document to use attributes (more on that in a moment) as columns instead, but elements can repeat and attributes cannot. In the following example, a directive starts an XML document, the root element (authors) is set, and the first names elements are repeated:
<?xml version="1.0" ?> <authors> <fname>Buck</fname> <fname>John</fname> <fname>Dianna</fname> <fname>Terri</fname> </authors>
XML Term: Element (Data)
Enclosed between element tags are the data they hold. In this example:
The Element tag is fname and the data it contains is Buck.
XML Term: Entity
An XML entity is basically an escape sequence. Since certain characters aren't valid inside an XML document (such as an ampersand (&)) an entity provides the ability to create these characters. You aren't limited to just nonstandard characters; you can create your own entities.
Using entities in this way is similar to either character string escapes in T-SQL or User Defined Data Types (UDT's).
You can also use entities to include binary data in an XML file. In this use they are similar to the BLOB data type in SQL Server.
Another use for entities are to stand in as "placeholders" that are filled at XML render-time. In this way, they are similar to table variables, temp tables, and the like.
Another way entities can be used is to link other XML documents, in-line. In this use, they are similar to a subquery.
XML Term: Attributes
Attributes are descriptors of an element. In the following example, author is now the element, and fname and lname are attributes:
<author fname="Buck" lname = "Woody">1234</fname>
You often see this arrangement when a part number is more significant than the text that describes it.
There's no direct corollary to a standard relational database object for attributes, assuming you've used elements as columns, although you could make the argument they are like check constraints. In future articles, I'll show you how to leverage this useful construct.
XML Term: XSLT
XSLT refers to another XML document, used to format the results of an XML file. XSLT is similar to formatting commands in T-SQL, but it's more powerful.
XML Term: DTD
Document Type Definitions are another form of XML that is paired with an XML document to specify how tags should be used. It's used to provide a limited form of referential integrity. It's more like programmatic referential integrity than declarative referential integrity, and is the earliest form of this kind of enforcement.
XML Term: Schema
A schema document is similar to a DTD, but is newer and has more features. Like DTDs, Schema documents enforce referential integrity.
XML Term: XPATH
XPath is a query specification using Style Sheets. While it is fairly basic, it is slightly similar to the Structured Query Language (SQL) syntax.
XML Term: Namespace
An XML namespace uniquely identifies the tags in a document. By using a namespace and appending your company's Web location, it enables the same type of behavior as a SQL Server's Global Identifier.
There's an inherent danger in comparing a hierarchical tagged file to a relational database, but I think it's useful to compare something with which you're familiar to something which you aren't. These comparisons will serve us well as we use XML as a data transfer mechanism in the next few articles.
Microsoft has a great XML site that has many references you can find here.
If you need a basic tutorial on XML, check out the earlier article I mentioned and then go here. This site provides a great place to start.