string-length Function (XQuery)

Switch View

string-length Function (XQuery)

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

string-length Function (XQuery)

Returns the length of the string in characters.

Syntax

fn:string-length() as xs:integer
fn:string-length($arg as xs:string?) as xs:integer

Arguments

$arg: Source string whose length is to be computed.

Remarks

If the value of $arg is an empty sequence, an xs:integer value of 0 is returned.

If the value contains a 3-byte Unicode character that is represented by two surrogate characters, SQL Server will count the surrogate characters individually.

The string-length() without a parameter can only be used inside a predicate. For example, the following query returns the <ROOT> element:

declare @x xml
set @x='<ROOT>Hello</ROOT>'
select @x.query('/ROOT[string-length()=5]')

Examples

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

A. Using the string-length() XQuery function to retrieve products with long summary descriptions

For products whose summary description is greater than 50 characters, the following query retrieves the product ID, the length of the summary description, and the summary itself, the <Summary> element.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' as pd)
SELECT CatalogDescription.query('
      <Prod ProductID= "{ /pd:ProductDescription[1]/@ProductModelID }" >
       <LongSummary SummaryLength = 
           "{string-length(string( (/pd:ProductDescription/pd:Summary)[1] )) }" >
           { string( (/pd:ProductDescription/pd:Summary)[1] ) }
       </LongSummary>
      </Prod>
 ') as Result
FROM Production.ProductModel
WHERE CatalogDescription.value('string-length( string( (/pd:ProductDescription/pd:Summary)[1]))', 'decimal') > 200

Note the following from the previous query:

The condition in the WHERE clause retrieves only the rows where the summary description stored in the XML document is longer than 200 characters. It uses the value() method (XML data type).
The SELECT clause just constructs the XML that you want. It uses the query() method (XML data type) to construct the XML and specify the necessary XQuery expression to retrieve data from the XML document.

This is a partial result:

Result
-------------------
<Prod ProductID="19">
      <LongSummary SummaryLength="214">Our top-of-the-line competition 
             mountain bike. Performance-enhancing options include the
             innovative HL Frame, super-smooth front suspension, and 
             traction for all terrain.
      </LongSummary>
</Prod>
...

B. Using the string-length() XQuery function to retrieve products whose warranty descriptions are very short

For products whose warranty descriptions are less than 20 characters long, the following query retrieves XML that includes the product ID, length, warranty description, and the <Warranty> element itself.

Warranty is one of the product features. An optional <Warranty> child element follows after the <Features> element.

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

SELECT CatalogDescription.query('
      for   $ProdDesc in /pd:ProductDescription,
            $pf in $ProdDesc/pd:Features/wm:Warranty
      where string-length( string(($pf/wm:Description)[1]) ) < 20
      return 
          <Prod >
             { $ProdDesc/@ProductModelID }
             <ShortFeature FeatureDescLength = 
                             "{string-length( string(($pf/wm:Description)[1]) ) }" >
                 { $pf }
             </ShortFeature>
          </Prod>
     ') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription')=1

Note the following from the previous query:

pd and wm are the namespace prefixes used in this query. They identify the same namespaces used in the document that is being queried.
The XQuery specifies a nested FOR loop. The outer FOR loop is required, because you want to retrieve the ProductModelID attributes of the <ProductDescription> element. The inner FOR loop is required, because you want only those products that have warranty feature descriptions that are less than 20 characters long.

This is the partial result:

Result
-------------------------
<Prod ProductModelID="19">
  <ShortFeature FeatureDescLength="15">
    <wm:Warranty 
       xmlns:wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain">
      <wm:WarrantyPeriod>3 years</wm:WarrantyPeriod>
      <wm:Description>parts and labor</wm:Description>
    </wm:Warranty>
   </ShortFeature>
</Prod>
...

Reference

XQuery Functions against the xml Data Type

Help and Information

Getting SQL Server 2008 Assistance