Functions on String Values - concat
Applies to:
SQL Server
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.
Supplementary Characters (Surrogate Pairs)
The behavior of surrogate pairs in XQuery functions depends on the database compatibility level and, in some cases, on the default namespace URI for functions. For more information, see the section "XQuery Functions Are Surrogate-Aware" in the topic Breaking Changes to Database Engine Features in SQL Server 2016. Also see ALTER DATABASE Compatibility Level (Transact-SQL) and Collation and Unicode Support.
Examples
This topic provides XQuery examples against XML instances that are stored in various xml type columns in the AdventureWorks sample 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.
See Also
Povratne informacije
https://aka.ms/ContentUserFeedback.
Kmalu na voljo: V letu 2024 bomo ukinili storitev Težave v storitvi GitHub kot mehanizem za povratne informacije za vsebino in jo zamenjali z novim sistemom za povratne informacije. Za več informacij si oglejte:Pošlji in prikaži povratne informacije za