Transfer Views – ‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *’

I am trying to transfer views from the AdventureWorks2014 database from one SQL Server to another using Java and JDBC.

In order to obtain the structure of the view, I use the stored procedure “sp_helptext” like this:

ResultSet rs = statement.executeQuery("EXEC sp_helptext '" + view + "';");

where view is the schema and view name (e.g., “dbo.myview”) that I am currently transferring.

When executing the command for the view “Person.vAdditionalContactInfo”, the following structure is returned:

CREATE VIEW [Person].[vAdditionalContactInfo]   
AS   
SELECT   
    [BusinessEntityID]   
    ,[FirstName]  
    ,[MiddleName]  
    ,[LastName]  
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber]   
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions]   
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
        (act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street]   
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City]   
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince]   
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') AS [PostalCode]   
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion]   
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions]   
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress]   
    ,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions]   
    ,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
        declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";   
        (act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber]   
    ,[rowguid]   
    ,[ModifiedDate]  
FROM [Person].[Person]  
OUTER APPLY [AdditionalContactInfo].nodes(  
    'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";   
    /ci:AdditionalContactInfo') AS ContactInfo(ref)   
WHERE [AdditionalContactInfo] IS NOT NULL;

The next step is to execute the retrieved statement on the target database:

try(Statement statement = connection.createStatement()) {
    // Retrieve the statement by concatenating every row of the result set
    StringBuilder sb = new StringBuilder();
    while(resultSet.next()) {
        sb.append(resultSet.getString(1));
    }
    // Execute the update statement 
    return statement.executeUpdate(sb.toString()) > 0;
} catch(SQLException e) {
    Logger.getLogger().severe("Exception occurred while transferring view!");
    Logger.getLogger().log(Level.SEVERE, e.getMessage(), e);
}

For every view in the AdventureWorks2014 db, this worked just fine. But for the “Person.vAdditionalContactInfo” view, the following error is returned after executing the update statement on the target SQL Server:

SEVERE: XQuery [Person.Person.AdditionalContactInfo.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
com.microsoft.sqlserver.jdbc.SQLServerException: XQuery [Person.Person.AdditionalContactInfo.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2930)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:711)
        at main.synchronizer.Synchronizer.transferView(Synchronizer.java:893)
        at main.synchronizer.Synchronizer.transferViews(Synchronizer.java:858)
        at main.synchronizer.Synchronizer.executeDirectMode(Synchronizer.java:135)
        at main.synchronizer.Synchronizer.run(Synchronizer.java:69)

I’ve looked around the internet for solutions, but haven’t found one. Any help would be greatly appreciated.

Answer

The error that occurred when trying to create the above-mentioned view was caused by a table column the view is referencing.

Before migrating all views of the AdventureWorks2014 database, I used a custom T-SQL script to generate table create statements to migrate tables similarly to the views. A bug in the script caused XML columns to be created as something different from an XML column, which explains why the access to one such column (AdditionalContactInfo of table Person.Person) that was expected to be an XML column was causing this error.