What Is XML in SQL Server: Tags, Attributes and Well-Formed Rules

What is XML in SQL Server? It’s plain text where every value carries its own label. You can open an XML file in Notepad and read it. This post builds one from a small table, then breaks it on purpose to show the rules.

The Sample Table

Every example uses four houseplants. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Plants (PlantID INT PRIMARY KEY, PlantName VARCHAR(20) NOT NULL, Light VARCHAR(10) NOT NULL, WaterDays INT NOT NULL);
INSERT INTO Plants (PlantID, PlantName, Light, WaterDays) VALUES
  (1, 'Basil', 'Sun', 2), (2, 'Fern', 'Shade', 3), (3, 'Mint', 'Shade', 2), (4, 'Aloe', 'Sun', 14);
GO

Data and Metadata

Metadata is data about data. In a query result, the column names are metadata and the values below them are the data. The grid shows each name once, in the header, and you match values to names by position.

SELECT PlantID, PlantName, Light, WaterDays FROM Plants ORDER BY PlantID;

SQL Server calls this a tabular result. It’s the default shape of every query. It’s compact because the names appear only once.

The Same Rows as XML

XML has no header row. Each value carries its own label, so nothing depends on position. Add FOR XML RAW to the same query to see it.

SELECT PlantID, PlantName, Light, WaterDays FROM Plants ORDER BY PlantID FOR XML RAW;

Each row became an element named row. Each column became an attribute, so the names PlantID, PlantName, Light and WaterDays now repeat four times. That repetition is the price of a self-describing format. In exchange, a program can read one row without seeing the rest.

Elements, Attributes and Text

An element starts with a start tag and ends with an end tag, such as <Plant> and </Plant>. An attribute is a name and a quoted value inside the start tag. Text sits between the tags. Elements can hold other elements, and that nesting is the structure.

Here is a small document written by hand. The outer element is the root, and everything else sits inside it.

DECLARE @Doc xml = N'<Plants>
  <Plant PlantID="1"><PlantName>Basil</PlantName><Light>Sun</Light></Plant>
  <Plant PlantID="2"><PlantName>Fern</PlantName><Light>Shade</Light></Plant>
</Plants>';
SELECT @Doc AS PlantsXml;

SQL Server kept the structure and dropped the line breaks and spaces between the elements. The output is one line. Indentation is for people, and tags are for programs.

Rules for Well-Formed XML

• One root element holds everything else.
• Every start tag has a matching end tag.
• Names are case sensitive, so Plant and plant are different.
• Attribute values are in quotes.
• The characters & and < are written as &amp; and &lt; inside data.

Break four of them on purpose. SQL Server checks them when it converts text to the xml data type. It rejects each one with its own message.

SELECT CAST(N'<Plant><PlantName>Basil</plantname></Plant>' AS xml);
GO

SELECT CAST(N'<Plant><PlantName>Basil</Plant>' AS xml);
GO

SELECT CAST(N'<Plant PlantID=1 />' AS xml);
GO

SELECT CAST(N'<Note>Salt & Pepper</Note>' AS xml);

The first two return message 9436, because an end tag doesn’t match its start tag. The third returns 9413, which expects a quoted string. The fourth returns 9421, an illegal name character, because a bare ampersand isn’t allowed in text.

The root rule works differently. The xml data type accepts two top-level elements, as the first query below shows. An XML document needs one root, so add it before another program reads the text. FOR XML takes a ROOT option for that, as the second query shows.

SELECT CAST(N'<Plant /><Plant />' AS xml) AS TwoTopLevelElements;

SELECT PlantID, PlantName FROM Plants ORDER BY PlantID FOR XML RAW('Plant'), ROOT('Plants');

You don’t have to escape anything when SQL Server writes the XML. The next query puts an ampersand and angle brackets in a value. The output replaces them with &amp;, &lt; and &gt;.

SELECT 'Salt & Pepper <mix>' AS Note FOR XML RAW;

Clean Up

Remove the sample table when you finish.

DROP TABLE Plants;

What to Remember

XML is data with its labels attached. Use FOR XML RAW to see your rows that way. Keep the five rules in mind when you write XML by hand. A missing quote or a tag in the wrong case is enough to stop the parser.

To go further, read FOR XML XSINIL: Emit Missing Fields as Explicit Nil Elements, Guest Post: SELECT * FROM XML by Jacob Sebastian and Introduction to Discovering XML Data Type Methods.

XML is not a table in disguise, it is data that carries its own labels.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.