SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 236 of 365 Next >

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:

<fname>Buck</fname>

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.

Online Resources

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.

InformIT Tutorials and Sample Chapters

The best resource for XML? Why it's our very own Nicholas Chase. He has an entire section on XML, and deals with databases in specific here.

< Back Page 236 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address