count Function (XQuery)
Returns the number of items that are contained in the sequence specified by $arg.
Syntax
fn:count($arg as item()*) as xs:integer
Arguments
- $arg
Items to count.
Remarks
Returns 0 if $arg is an empty sequence.
Examples
This topic provides XQuery examples against XML instances that are 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 count() XQuery function to count the number of work center locations in the manufacturing of a product model
The following query counts the number of work center locations in the manufacturing process of a product model (ProductModelID=7).
SELECT Production.ProductModel.ProductModelID,
Production.ProductModel.Name,
Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
<NoOfWorkStations>
{ count(/AWMI:root/AWMI:Location) }
</NoOfWorkStations>
') as WorkCtrCount
FROM Production.ProductModel
WHERE Production.ProductModel.ProductModelID=7
Note the following from the previous query:
The namespace keyword in XQuery Prolog defines a namespace prefix. The prefix is then used in the XQuery body.
The query constructs XML that includes the <NoOfWorkStations> element.
The count() function in the XQuery body counts the number of <Location> elements.
This is the result:
ProductModelID Name WorkCtrCount
-------------- ---------------------------------------------------
7 HL Touring Frame <NoOfWorkStations>6</NoOfWorkStations>
You can also construct the XML to include the product model ID and name, as shown in the following query:
SELECT Instructions.query('
declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
<NoOfWorkStations
ProductModelID= "{ sql:column("Production.ProductModel.ProductModelID") }"
ProductModelName = "{ sql:column("Production.ProductModel.Name") }" >
{ count(/AWMI:root/AWMI:Location) }
</NoOfWorkStations>
') as WorkCtrCount
FROM Production.ProductModel
WHERE Production.ProductModel.ProductModelID= 7
This is the result:
<NoOfWorkStations ProductModelID="7"
ProductModelName="HL Touring Frame">6</NoOfWorkStations>
Instead of XML, you may return these values as non-xml type, as shown in the following query. The query uses the value() method (xml data type) to retrieve the work center location count.
SELECT ProductModelID,
Name,
Instructions.value('declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
count(/AWMI:root/AWMI:Location)', 'int' ) as WorkCtrCount
FROM Production.ProductModel
WHERE ProductModelID=7
This is the result:
ProductModelID Name WorkCtrCount
-------------- ---------------------------------
7 HL Touring Frame 6