XML construction (XQuery)

2025-09-29

Applies to: SQL Server

In XQuery, you can use the direct and computed constructors to construct XML structures within a query.

Note

There's no difference between the direct and computed constructors.

Use direct constructors

When you use direct constructors, you specify XML-like syntax when you construct the XML. The following examples illustrate XML construction by the direct constructors.

Construct elements

In using XML notations, you can construct elements. The following example uses the direct element constructor expression and creates a <ProductModel> element. The constructed element has three child elements

A text node.
Two element nodes, <Summary> and <Features>.
- The <Summary> element has one text node child whose value is "Some description".
- The <Features> element has three element node children, <Color>, <Weight>, and <Warranty>. Each of these nodes has one text node child and have the values Red, 25, 2 years parts and labor, respectively.

DECLARE @x AS XML;

SET @x = '';

SELECT @x.query('<ProductModel ProductModelID="111">;
This is product model catalog description.
<Summary>Some description</Summary>
<Features>
  <Color>Red</Color>
  <Weight>25</Weight>
  <Warranty>2 years parts and labor</Warranty>
</Features></ProductModel>');

Here's the resulting XML:

<ProductModel ProductModelID="111">
  This is product model catalog description.
  <Summary>Some description</Summary>
  <Features>
    <Color>Red</Color>
    <Weight>25</Weight>
    <Warranty>2 years parts and labor</Warranty>
  </Features>
</ProductModel>

Although constructing elements from constant expressions, as shown in this example, is useful, the true power of this XQuery language feature is the ability to construct XML that dynamically extracts data from a database. You can use curly braces to specify query expressions. In the resulting XML, the expression is replaced by its value. For example, the following query constructs a <NewRoot> element with one child element (<e>). The value of element <e> is computed by specifying a path expression inside curly braces ("{ ... }").

DECLARE @x AS XML;

SET @x = '<root>5</root>';

SELECT @x.query('<NewRoot><e> { /root } </e></NewRoot>');

The braces act as context-switching tokens and switch the query from XML construction to query evaluation. In this case, the XQuery path expression inside the braces, /root, is evaluated and the results are substituted for it.

Here's the result:

<NewRoot>
  <e>
    <root>5</root>
  </e>
</NewRoot>

The following query is similar to the previous one. However, the expression in the curly braces specifies the data() function to retrieve the atomic value of the <root> element and assigns it to the constructed element, <e>.

DECLARE @x AS XML;
SET @x = '<root>5</root>';

DECLARE @y AS XML;
SET @y = (SELECT @x.query('
                           <NewRoot>
                             <e> { data(/root) } </e>
                           </NewRoot>'));

SELECT @y;

Here's the result:

<NewRoot>
  <e>5</e>
</NewRoot>

If you want to use the curly braces as part of your text instead of context-switching tokens, you can escape them as "}}" or "{{", as shown in this example:

DECLARE @x AS XML;
SET @x = '<root>5</root>';

DECLARE @y AS XML;
SET @y = (SELECT @x.query('
<NewRoot> Hello, I can use {{ and  }} as part of my text</NewRoot>'));

SELECT @y;

Here's the result:

<NewRoot> Hello, I can use { and  } as part of my text</NewRoot>

The following query is another example of constructing elements by using the direct element constructor. Also, the value of the <FirstLocation> element is obtained by executing the expression in the curly braces. The query expression returns the manufacturing steps at the first work center location from the Instructions column of the Production.ProductModel table.

SELECT Instructions.query('
    declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
        <FirstLocation>
           { /AWMI:root/AWMI:Location[1]/AWMI:step }
        </FirstLocation>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Here's the result:

<FirstLocation>
  <AWMI:step xmlns:AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
      Insert <AWMI:material>aluminum sheet MS-2341</AWMI:material> into the <AWMI:tool>T-85A framing tool</AWMI:tool>.
  </AWMI:step>
  <AWMI:step xmlns:AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
      Attach <AWMI:tool>Trim Jig TJ-26</AWMI:tool> to the upper and lower right corners of the aluminum sheet.
  </AWMI:step>
   ...
</FirstLocation>

Element content in XML construction

The following example illustrates the behavior of the expressions in constructing element content by using the direct element constructor. In the following example, the direct element constructor specifies one expression. For this expression, one text node is created in the resulting XML.

DECLARE @x AS XML;
SET @x = '
<root>
  <step>This is step 1</step>
  <step>This is step 2</step>
  <step>This is step 3</step>
</root>';

SELECT @x.query('
<result>
 { for $i in /root[1]/step
    return string($i)
 }
</result>');

The atomic value sequence resulting from the expression evaluation is added to the text node with a space added between the adjacent atomic values, as shown in the result. The constructed element has one child. This is a text node that contains the value shown in the result.

<result>This is step 1 This is step 2 This is step 3</result>

Instead of one expression, if you specify three separate expressions generating three text nodes, the adjacent text nodes are merged into a single text node, by concatenation, in the resulting XML.

DECLARE @x AS XML;
SET @x = '
<root>
  <step>This is step 1</step>
  <step>This is step 2</step>
  <step>This is step 3</step>
</root>';

SELECT @x.query('
<result>
 { string(/root[1]/step[1]) }
 { string(/root[1]/step[2]) }
 { string(/root[1]/step[3]) }
</result>');

The constructed element node has one child. This is a text node that contains the value shown in the result.

<result>This is step 1This is step 2This is step 3</result>

Construct attributes

When you're constructing elements by using the direct element constructor, you can also specify attributes of the element by using XML-like syntax, as shown in this example:

DECLARE @x AS XML;
SET @x = '';

SELECT @x.query('<ProductModel ProductModelID="111">;
This is product model catalog description.
<Summary>Some description</Summary>
</ProductModel>');

Here's the resulting XML:

<ProductModel ProductModelID="111">
  This is product model catalog description.
  <Summary>Some description</Summary>
</ProductModel>

The constructed element <ProductModel> has a ProductModelID attribute and these child nodes:

A text node, This is product model catalog description.
An element node, <Summary>. This node has one text node child, Some description.

When you're constructing an attribute, you can specify its value with an expression in curly braces. In this case, the result of the expression is returned as the attribute value.

In the following example, the data() function isn't strictly required. Because you're assigning the expression value to an attribute, data() is implicitly applied to retrieve the typed value of the specified expression.

DECLARE @x AS XML;
SET @x = '<root>5</root>';

DECLARE @y AS XML;
SET @y = (SELECT @x.query('<NewRoot attr="{ data(/root) }" ></NewRoot>'));

SELECT @y;

Here's the result:

<NewRoot attr="5" />

Following is another example in which expressions are specified for LocationID and SetupHrs attribute construction. These expressions are evaluated against the XML in the Instruction column. The typed valued of the expression is assigned to the attributes.

SELECT Instructions.query('
    declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
        <FirstLocation
         LocationID="{ (/AWMI:root/AWMI:Location[1]/@LocationID)[1] }"
         SetupHrs = "{ (/AWMI:root/AWMI:Location[1]/@SetupHours)[1] }" >
           { /AWMI:root/AWMI:Location[1]/AWMI:step }
        </FirstLocation>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Here's the partial result:

<FirstLocation LocationID="10" SetupHours="0.5" >
  <AWMI:step ...
  </AWMI:step>
  ...
</FirstLocation>

Implementation limitations

These are the limitations:

Multiple or mixed (string and XQuery expression) attribute expressions aren't supported. For example, as shown in the following query, you construct XML where Item is a constant and the value 5 is obtained by evaluating a query expression:
```
<a attr="Item 5" />
```
The following query returns an error, because you're mixing constant string with an expression ({/x}) and this isn't supported:
```
DECLARE @x AS XML;
SET @x = '<x>5</x>';

SELECT @x.query('<a attr="Item {/x}"/>');
```
In this case, you have the following options:
- Form the attribute value by the concatenation of two atomic values. These atomic values are serialized into the attribute value with a space between the atomic values:
```
SELECT @x.query('<a attr="{''Item'', data(/x)}"/>');
```
  Here's the result:
```
<a attr="Item 5" />
```
- Use the concat function to concatenate the two string arguments into the resulting attribute value:
```
SELECT @x.query('<a attr="{concat(''Item'', /x[1])}"/>');
```
  In this case, there's no space added between the two string values. If you want a space between the two values, you must explicitly provide it.
  
  Here's the result:
```
<a attr="Item5" />
```
Multiple expressions as an attribute value aren't supported. For example, the following query returns an error:
```
DECLARE @x AS XML;
SET @x = '<x>5</x>';

SELECT @x.query('<a attr="{/x}{/x}"/>');
```
Heterogeneous sequences aren't supported. Any attempt to assign a heterogeneous sequence as an attribute value returns an error, as shown in the following example. In this example, a heterogeneous sequence, a string "Item" and an element <x>, is specified as the attribute value:
```
DECLARE @x AS XML;
SET @x = '<x>5</x>';

SELECT @x.query('<a attr="{''Item'', /x }" />');
```
If you apply the data() function, the query works because it retrieves the atomic value of the expression, /x, which is concatenated with the string. Following is a sequence of atomic values:
```
SELECT @x.query('<a attr="{''Item'', data(/x)}"/>');
```
Here's the result:
```
<a attr="Item 5" />
```

Attribute node order is enforced during serialization rather than during static type checking. For example, the following query fails because it attempts to add an attribute after a non-attribute node.

SELECT CONVERT (XML, '').query('
    element x { attribute att { "pass" }, element y { "Element text" }, attribute att2 { "fail" } }
    ');
GO

The previous query returns the following error:

XML well-formedness check: Attribute cannot appear outside of element declaration. Rewrite your XQuery so it returns well-formed XML.

Add namespaces

When constructing XML by using the direct constructors, the constructed element and attribute names can be qualified by using a namespace prefix. You can bind the prefix to the namespace in the following ways:

By using a namespace declaration attribute.
By using the WITH XMLNAMESPACES clause.
In the XQuery prolog.

Use a namespace declaration attribute to add namespaces

The following example uses a namespace declaration attribute in the construction of element <a> to declare a default namespace. The construction of the child element <b> undoes the declaration of the default namespace declared in the parent element.

DECLARE @x AS XML;
SET @x = '<x>5</x>';

SELECT @x.query('
  <a xmlns="a">
    <b xmlns=""/>
  </a>');

Here's the result:

<a xmlns="a">
  <b xmlns="" />
</a>

You can assign a prefix to the namespace. The prefix is specified in the construction of element <a>.

DECLARE @x AS XML;
SET @x = '<x>5</x>';

SELECT @x.query('
  <x:a xmlns:x="a">
    <b/>
  </x:a>');

Here's the result:

<x:a xmlns:x="a">
  <b />
</x:a>

You can un-declare a default namespace in the XML construction, but you can't un-declare a namespace prefix. The following query returns an error, because you can't un-declare a prefix as specified in the construction of element <b>.

DECLARE @x AS XML;
SET @x = '<x>5</x>';

SELECT @x.query('
  <x:a xmlns:x="a">
    <b xmlns:x=""/>
  </x:a>');

The newly constructed namespace is available to use inside the query. For example, the following query declares a namespace in constructing the element, <FirstLocation>, and specifies the prefix in the expressions for the LocationID and SetupHrs attribute values.

SELECT Instructions.query('
        <FirstLocation xmlns:AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
         LocationID="{ (/AWMI:root/AWMI:Location[1]/@LocationID)[1] }"
         SetupHrs = "{ (/AWMI:root/AWMI:Location[1]/@SetupHours)[1] }" >
           { /AWMI:root/AWMI:Location[1]/AWMI:step }
        </FirstLocation>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Creating a new namespace prefix in this way overrides any preexisting namespace declaration for this prefix. For example, the namespace declaration, AWMI="https://someURI", in the query prolog is overridden by the namespace declaration in the <FirstLocation> element.

SELECT Instructions.query('
declare namespace AWMI="https://someURI";
        <FirstLocation xmlns:AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
         LocationID="{ (/AWMI:root/AWMI:Location[1]/@LocationID)[1] }"
         SetupHrs = "{ (/AWMI:root/AWMI:Location[1]/@SetupHours)[1] }" >
           { /AWMI:root/AWMI:Location[1]/AWMI:step }
        </FirstLocation>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Use a prolog to add namespaces

This example illustrates how namespaces can be added to the constructed XML. A default namespace is declared in the query prolog.

DECLARE @x AS XML;
SET @x = '<x>5</x>';

SELECT @x.query('
           declare default element namespace "a";
            <a><b xmlns=""/></a>');

In the construction of element <b>, the namespace declaration attribute is specified with an empty string as its value. This un-declares the default namespace that is declared in the parent.

Here's the result:

<a xmlns="a">
  <b xmlns="" />
</a>

XML construction and white space handling

The element content in XML construction can include white-space characters. These characters are handled in the following ways:

The white-space characters in namespace URIs are treated as the XSD type anyURI. Specifically, this is how they're handled:
- Any white-space characters at the start and end are trimmed.
- Internal white-space character values are collapsed into a single space
The linefeed characters inside the attribute content are replaced by spaces. All other white-space characters remain as they are.
The white space inside elements is preserved.

The following example illustrates white-space handling in XML construction:

-- line feed is replaced by space.
DECLARE @x AS XML;
SET @x = '';

SELECT @x.query('

declare namespace myNS="   https://
 abc/
xyz

";
<test attr="    my
test   attr
value    " >

<a>

This     is  a

test

</a>
</test>
') AS XML_Result;

Here's the result:

-- result
<test attr="<test attr="    my test   attr  value    "><a>

This     is  a

test

</a></test>
"><a>

This     is  a

test

</a></test>

Other direct XML constructors

The constructors for processing instructions and XML comments use the same syntax as the corresponding XML construct syntax. Computed constructors for text nodes are also supported, but are primarily used in XML DML to construct text nodes.

Note

For an example of using an explicit text node constructor, see the specific example in insert (XML DML).

In the following query, the constructed XML includes an element, two attributes, a comment, and a processing instruction. A comma is used before the <FirstLocation>, because a sequence is being constructed.

SELECT Instructions.query('
  declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
   <?myProcessingInstr abc="value" ?>,
   <FirstLocation
        WorkCtrID = "{ (/AWMI:root/AWMI:Location[1]/@LocationID)[1] }"
        SetupHrs = "{ (/AWMI:root/AWMI:Location[1]/@SetupHours)[1] }" >
       <!-- some comment -->
       <?myPI some processing instructions ?>
       { (/AWMI:root/AWMI:Location[1]/AWMI:step) }
    </FirstLocation>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Here's the partial result:

<?myProcessingInstr abc="value" ?>
<FirstLocation WorkCtrID="10" SetupHrs="0.5">
  <!-- some comment -->
  <?myPI some processing instructions ?>
  <AWMI:step xmlns:AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">I
  nsert <AWMI:material>aluminum sheet MS-2341</AWMI:material> into the <AWMI:tool>T-85A framing tool</AWMI:tool>.
  </AWMI:step>
    ...
</FirstLocation>

Use computed constructors

In this case, you specify the keywords that identify the type of node you want to construct. Only the following keywords are supported:

element
attribute
text

For element and attribute nodes, these keywords are followed by node name and also by the expression, enclosed in braces, that generates the content for that node. In the following example, you're constructing this XML:

<root>
  <ProductModel PID="5">Some text <summary>Some Summary</summary></ProductModel>
</root>

Here's the query that uses computed constructors do generate the XML:

DECLARE @x AS XML;
SET @x = '';

SELECT @x.query('element root
               {
                  element ProductModel
     {
attribute PID { 5 },
text{"Some text "},
    element summary { "Some Summary" }
 }
               } ');

The expression that generates the node content can specify a query expression.

DECLARE @x AS XML;
SET @x = '<a attr="5"><b>some summary</b></a>';

SELECT @x.query('element root
               {
                  element ProductModel
     {
attribute PID { /a/@attr },
text{"Some text "},
    element summary { /a/b }
 }
               } ');

The computed element and attribute constructors, as defined in the XQuery specification, allow you to compute the node names. When you're using direct constructors in SQL Server, the node names, such as element and attribute, must be specified as constant literals. Therefore, there's no difference in the direct constructors and computed constructors for elements and attributes.

In the following example, the content for the constructed nodes is obtained from the XML manufacturing instructions stored in the Instructions column of the xml data type in the ProductModel table.

SELECT Instructions.query('
  declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
   element FirstLocation
     {
        attribute LocationID { (/AWMI:root/AWMI:Location[1]/@LocationID)[1] },
        element   AllTheSteps { /AWMI:root/AWMI:Location[1]/AWMI:step }
     }
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Here's the partial result:

<FirstLocation LocationID="10">
  <AllTheSteps>
    <AWMI:step> ... </AWMI:step>
    <AWMI:step> ... </AWMI:step>
    ...
  </AllTheSteps>
</FirstLocation>

Other implementation limitations

Computed attribute constructors can't be used to declare a new namespace. Also, the following computed constructors aren't supported in SQL Server:

Computed document node constructors
Computed processing instruction constructors
Computed comment constructors

XQuery Expressions

Feedback

Was this page helpful?

Share via

XML construction (XQuery)

Use direct constructors

Construct elements

Element content in XML construction

Construct attributes

Implementation limitations

Add namespaces

Use a namespace declaration attribute to add namespaces

Use a prolog to add namespaces

XML construction and white space handling

Other direct XML constructors

Use computed constructors

Other implementation limitations

Related content

Feedback

Additional resources