Article
05/23/2011

concat Function (XQuery)

Accepts zero or more strings as arguments and returns a string created by concatenating the values of each of these arguments.

Syntax

fn:concat ($string as xs:string?
           ,$string as xs:string?
           [, ...]) as xs:string

Arguments

$string
Optional string to concatenate.

Remarks

The function requires at least two arguments. If an argument is an empty sequence, it is treated as the zero-length string.

Examples

This topic provides XQuery examples against XML instances that are stored in various xml type columns in the AdventureWorks2008R2 sample database. For an overview of these columns, see xml Data Type Representation in the AdventureWorks2008R2 Database.

A. Using the concat() XQuery function to concatenate strings

For a specific product model, this query returns a string created by concatenating the warranty period and warranty description. In the catalog description document, the <Warranty> element is made up of <WarrantyPeriod> and <Description> child elements.

WITH XMLNAMESPACES (
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd,
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm)
SELECT CatalogDescription.query('
    <Product 
        ProductModelID= "{ (/pd:ProductDescription/@ProductModelID)[1] }"
        ProductModelName = "{ sql:column("PD.Name") }" >
        { 
          concat( string((/pd:ProductDescription/pd:Features/wm:Warranty/wm:WarrantyPeriod)[1]), "-",
                  string((/pd:ProductDescription/pd:Features/wm:Warranty/wm:Description)[1])) 
         } 
     </Product>
 ') as Result
FROM Production.ProductModel PD
WHERE  PD.ProductModelID=28;

Note the following from the previous query:

In the SELECT clause, CatalogDescription is an xml type column. Therefore, the query() method (XML data type), Instructions.query(), is used. The XQuery statement is specified as the argument to the query method.
The document against which the query is executed uses namespaces. Therefore, the namespace keyword is used to define the prefix for the namespace. For more information, see XQuery Prolog.

This is the result:

<Product ProductModelID="28" ProductModelName="Road-450">1 year-parts and labor</Product>

The previous query retrieves information for a specific product. The following query retrieves the same information for all the products for which XML catalog descriptions are stored. The exist() method of the xml data type in the WHERE clause returns True if the XML document in the rows has a <ProductDescription> element.

WITH XMLNAMESPACES (
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd,
'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm)

SELECT CatalogDescription.query('
    <Product 
        ProductModelID= "{ (/pd:ProductDescription/@ProductModelID)[1] }" 
        ProductName = "{ sql:column("PD.Name") }" >
        { 
          concat( string((/pd:ProductDescription/pd:Features/wm:Warranty/wm:WarrantyPeriod)[1]), "-",
                  string((/pd:ProductDescription/pd:Features/wm:Warranty/wm:Description)[1])) 
         } 
     </Product>
 ') as Result
FROM Production.ProductModel PD
WHERE CatalogDescription.exist('//pd:ProductDescription ') = 1

Note that the Boolean value returned by the exist() method of the xml type is compared with 1.

Implementation Limitations

These are the limitations:

The concat() function in SQL Server only accepts values of type xs:string. Other values have to be explicitly cast to xs:string or xdt:untypedAtomic.

Share via