Export (0) Print
Expand All

string-length Function (XQuery)

Returns the length of the string in characters.


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

$arg

Source string whose length is to be computed.

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]');

This topic provides XQuery examples against XML instances stored in various xml type columns in the AdventureWorks2008R2 database. For an overview of each of these columns, see xml Data Type Representation in the AdventureWorks2008R2 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>

...

Community Additions

ADD
Show:
© 2015 Microsoft