时间戳

首页 > 解决方案 > 转移视图 - 'value()' 需要一个单例（或空序列），找到类型为 'xdt:untypedAtomic *' 的操作数

java - 转移视图 - 'value()' 需要一个单例（或空序列），找到类型为 'xdt:untypedAtomic *' 的操作数

问题描述

我正在尝试使用 Java 和 JDBC 将 AdventureWorks2014 数据库中的视图从一个 SQL Server 传输到另一个。

为了获得视图的结构，我像这样使用存储过程“sp_helptext”：

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

其中 view 是我当前正在传输的架构和视图名称（例如，“dbo.myview”）。

执行视图“Person.vAdditionalContactInfo”的命令时，返回以下结构：

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;

下一步是在目标数据库上执行检索到的语句：

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);
}

对于 AdventureWorks2014 数据库中的每个视图，这都很好。但是对于“Person.vAdditionalContactInfo”视图，在目标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)

我在互联网上四处寻找解决方案，但还没有找到。任何帮助将不胜感激。

标签： javasql-serverjdbc

解决方案

尝试创建上述视图时发生的错误是由视图引用的表列引起的。

在迁移 AdventureWorks2014 数据库的所有视图之前，我使用自定义 T-SQL 脚本生成表创建语句，以类似于视图迁移表。脚本中的一个错误导致 XML 列被创建为与 XML 列不同的东西，这解释了为什么对预期是 XML 列的一个这样的列（AdditionalContactInfo 表 ）的访问会导致此错误。Person.Person

推荐阅读