Sql-server – How to cast a list of values within xquery in SQL Server

sql serverxquery

I have the following T-SQL code that I am trying to get to work:

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'TST') DROP XML SCHEMA COLLECTION TST; 
GO
CREATE XML SCHEMA COLLECTION TST AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="root"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="val" minOccurs="0" maxOccurs="unbounded" type="xsd:string" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>';
GO
DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';

SELECT @xml.value('sum(//val)','INT')

The xsd tells us that the val node is of type string. Therefor the sum() function won't work:

Msg 9308, Level 16, State 1, Line 7
XQuery [value()]: The argument of 'sum()' must be of a single numeric primitive type or 'http://www.w3.org/2004/07/xpath-datatypes#untypedAtomic'. Found argument of type 'xs:string *'.

I have tried the following ways to get this to work:

Option 1:

One work-around is to just get rid of the xsd schema:

DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT (CAST(@xml AS XML)).value('sum(//val)','INT')

That returns the correct result: 6. However, it seems rather crude and might not always be an option (e.g. when dealing with XML indexes).

(Non-)Option 2:

An alternative is, to cast each value to xs:integer before summing them together. However, that is where I am stuck.

It is easy to cast a single element:

DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT @xml.value('sum(xs:integer((//val)[1]))','INT')

But that results in 1, which is the wrong result. This is expected, as now (because of the [1]) only the first val node gets looked at.

(Non-)Option 3:

There are two ways to write a cast in xquery:

DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT @xml.value('sum(xs:integer((//val)))','INT')
GO
DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT @xml.value('sum(((//val) cast as xs:integer ?))','INT')

However, both work only with singletons as input:

Msg 2365, Level 16, State 1, Line 15
XQuery [value()]: Cannot explicitly convert from 'xs:string *' to 'xs:integer'
Msg 2365, Level 16, State 1, Line 18
XQuery [value()]: Cannot explicitly convert from 'xs:string *' to 'xs:integer ?'

(Non-)Option 4:

According to the xquery documentation you should be able to do this:

DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT @xml.value('sum((//val)! xs:integer(.))','INT')

However, that is seems to not be implemented by SQL Server:

Msg 2217, Level 16, State 1, Line 21
XQuery [value()]: ',' or ')' expected

Option 5:

You could use the .nodes() function and solve the problem outside of xquery:

DECLARE @xml XML(TST) = '<root><val>1</val><val>2</val><val>3</val></root>';
SELECT SUM(X.V.value('.','INT')) FROM @xml.nodes('//val') X(V)

This results in the correct value of 6. However, I am looking for a solution within xquery, so this is not an option for me.

Option ?:

With that I am out of ideas. Is there a solution to this problem?

Extra:

In addition, I would really like to know what the ? stands for in this line, taken from one of the examples above:

SELECT @xml.value('sum(((//val)[1] cast as xs:integer ?))','INT')

SQL Server requires it. You get this error, if you try without:

Msg 9301, Level 16, State 1, Line 18
XQuery [value()]: In this version of the server, 'cast as <type>' is not available. Please use the 'cast as <type> ?' syntax.

This error hints at the fact, that this requirement is a limitation of SQL Server. However, I was not able to find documentation about its meaning anywhere.

Best Answer

You can do it in xQuery with a FLWOR Statement and Iteration (XQuery) using cast on each value returned.

SELECT @xml.value('sum(for $val in //val return $val cast as xs:int?)','INT')

A query like that could make you think that SQL Server will do some time consuming looping operations but if you have a look at the query plan you will see that is not the case. The for loop is transformed to a plan that gets all the values from a single call to a Table-valued function, converts them to an integer and then using an aggregate operator to calculate the sum.

Related Solutions

Sql-server – use attribute name to identify XML element

The .value method must always return a single value so you normally wrap it in brackets and move the ordinal ( eg [1] ) to the end. Here's an example:

DECLARE @xml AS XML = '<DATA>
  <ROW RowNumber="1">
    <COLUMN Name="ProdID" Value="1234" />
    <COLUMN Name="AppID" Value="20144" />
    <COLUMN Name="ConsentDesc" Value="My Description" />
    <COLUMN Name="ConsentVer" Value="" />
    <COLUMN Name="ConsentAcceptance" Value="6789" />
    <COLUMN Name="ConsentType" Value="TermsAndConditions" />
    <COLUMN Name="CreatedDate" Value="8/22/2014 3:04:29 PM" />
  </ROW>
</DATA>'

SELECT  m.c.value('@RowNumber', 'varchar(50)') AS RowNumber ,
        m.c.value('(COLUMN[@Name="ProdID"]/@Value)[1]', 'varchar(50)') AS [ProdID] ,
        m.c.value('(COLUMN[@Name="AppID"]/@Value)[1]', 'varchar(50)') AS [AppID] ,
        m.c.value('(COLUMN[@Name="ConsentDesc"]/@Value)[1]', 'varchar(50)') AS [ConsentDesc.] ,
        m.c.value('(COLUMN[@Name="ConsentVer"]/@Value)[1]', 'varchar(50)') AS [ConsentVer.] ,
        m.c.value('(COLUMN[@Name="ConsentAcceptance"]/@Value)[1]', 'varchar(50)') AS [ConsentAcceptance] ,
        m.c.value('(COLUMN[@Name="ConsentType"]/@Value)[1]', 'varchar(50)') AS [ConsentType] ,
        m.c.value('(COLUMN[@Name="CreatedDate"]/@Value)[1]', 'varchar(50)') AS [CreatedDate]
FROM    @xml.nodes('DATA/ROW') AS m ( c )

Sql-server – Replace value of strongly typed xml element in SQL Server with XQuery

I have not found a simple way to just modify the replace value of statement to work with anonymous simple type definitions.

Simple repro of what you have:

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid">
                    <xs:simpleType>
                        <xs:restriction base="xs:string">
                            <xs:maxLength value="30"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

set @X.modify('replace value of /root/xid  with "2"');

Result:

Msg 2247, Level 16, State 1, Line 25 XQuery [modify()]: The value is of type "xs:string", which is not a subtype of the expected type "<anonymous>".

One workaround is to modify your schema to use a named simple type xidType and cast the new value.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid" type="xidType"/>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:simpleType name="xidType">
        <xs:restriction base="xs:string">
            <xs:maxLength value="30"/>
        </xs:restriction>
    </xs:simpleType>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

set @X.modify('replace value of /root/xid  with "2" cast as xidType?');

Another way is to extract the XML to an untyped XML variable, modify the variable and put it back to the table.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as 
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid">
                    <xs:simpleType>
                        <xs:restriction base="xs:string">
                            <xs:maxLength value="30"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';
declare @X2 xml = @X;

set @X2.modify('replace value of (/root/xid/text())[1]  with "2"');
set @X = @X2;

Best Answer

Related Solutions

Sql-server – use attribute name to identify XML element

Sql-server – Replace value of strongly typed xml element in SQL Server with XQuery

Related Question