Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Conditional Expressions (XQuery)

Conditional Expressions (XQuery)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

XQuery supports the following conditional if-then-else statement:

if (<expression1>)
then
  <expression2>
else
  <expression3>

Depending on the effective Boolean value of expression1, either expression2 or expression3 is evaluated. For example:

  • If the test expression, expression1, results in an empty sequence, the result is False.

  • If the test expression, expression1, results in a simple Boolean value, this value is the result of the expression.

  • If the test expression, expression1, results in a sequence of one or more nodes, the result of the expression is True.

  • Otherwise, a static error is raised.

Also note the following:

  • The test expression must be enclosed between parentheses.

  • The else expression is required. If you do not need it, you can return " ( ) ", as illustrated in the examples in this topic.

For example, the following query is specified against the xml type variable. The if condition tests the value of the SQL variable (@v) inside the XQuery expression by using the sql:variable() function extension function. If the variable value is "FirstName", it returns the <FirstName> element. Otherwise, it returns the <LastName> element.

declare @x xml
declare @v varchar(20)
set @v='FirstName'
set @x='
<ROOT rootID="2">
  <FirstName>fname</FirstName>
  <LastName>lname</LastName>
</ROOT>'
SELECT @x.query('
if ( sql:variable("@v")="FirstName" ) then
  /ROOT/FirstName
 else
   /ROOT/LastName
')

This is the result:

<FirstName>fname</FirstName>

The following query retrieves the first two feature descriptions from the product catalog description of a specific product model. If there are more features in the document, it adds a <there-is-more> element with empty content.

SELECT CatalogDescription.query('
     declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
     <Product> 
          { /p1:ProductDescription/@ProductModelID }
          { /p1:ProductDescription/@ProductModelName } 
          {
            for $f in /p1:ProductDescription/p1:Features/*[position()<=2]
            return
            $f 
          }
          {
            if (count(/p1:ProductDescription/p1:Features/*) > 2)
            then <there-is-more/>
            else ()
          } 
     </Product>        
') as x
FROM Production.ProductModel
WHERE ProductModelID = 19

In the previous query, the condition in the if expression checks whether there are more than two child elements in <Features>. If yes, it returns the <there-is-more/> element in the result.

This is the result:

<Product ProductModelID="19" ProductModelName="Mountain 100">
  <p1:Warranty xmlns:p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
    <p1:WarrantyPeriod>3 years</p1:WarrantyPeriod>
    <p1:Description>parts and labor</p1:Description>
  </p1:Warranty>
  <p2:Maintenance xmlns:p2="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
    <p2:NoOfYears>10 years</p2:NoOfYears>
    <p2:Description>maintenance contract available through your dealer or any AdventureWorks retail store.</p2:Description>
  </p2:Maintenance>
  <there-is-more />
</Product>

In the following query, a <Location> element with a LocationID attribute is returned if the work center location does not specify the setup hours.

SELECT Instructions.query('
     declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
        for $WC in //AWMI:root/AWMI:Location
        return
        if ( $WC[not(@SetupHours)] )
        then
          <WorkCenterLocation>
             { $WC/@LocationID } 
          </WorkCenterLocation>
         else
           ()
') as Result
FROM Production.ProductModel
where ProductModelID=7

This is the result:

<WorkCenterLocation LocationID="30" />
<WorkCenterLocation LocationID="45" />
<WorkCenterLocation LocationID="60" />

This query can be written without the if clause, as shown in the following example:

SELECT Instructions.query('
     declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $WC in //AWMI:root/AWMI:Location[not(@SetupHours)] 
        return
          <Location>
             { $WC/@LocationID } 
          </Location>
') as Result
FROM Production.ProductModel
where ProductModelID=7

Community Additions

ADD
Show:
© 2015 Microsoft