我正在解析一个 xml 输入，使用 xQuery这有助于在存储过程中解析 xml 文件，如 here , 然后将它们插入到表中。我能够将 XML 中的值正确插入表中。

但是，我想插入一个新创建的 UUID 列作为我进行插入的表的第一列，只有其余列的值需要来自 XML。由于 xQuery 批量插入，我创建了一个包含 UUID 的临时表，并尝试将其与从 XML 解析的其余值一起插入。但是，它给了我一个错误。

--SP
CREATE PROCEDURE dbo.ParseXML
   @XML XML
AS
BEGIN
    DECLARE @applicationId NVARCHAR(36);
    DECLARE @dashboardcount INT;
    DECLARE @dashboardscount INT;
    DECLARE @applicationcount INT;

    select @applicationId = NEWID();
    select @dashboardcount = Book.value('count(/application/dashboards/dashboard)', 'NVARCHAR(100)') FROM @XML.nodes('application/dashboards/dashboard')Catalog(Book)    

    drop table if exists #MyList
    create table #MyList (
    id nvarchar( 36 ) not null
    )
    DECLARE @LoopCounter INT = 1
    WHILE ( @LoopCounter <= @dashboardcount)
    BEGIN
        insert #MyList values ( NEWID() ) 
        SET @LoopCounter  = @LoopCounter  + 1
    END
    select * from #MyList

    --insert into applications (id, alias, title, description) (select @applicationId as id, Book.value('alias[1]','NVARCHAR(100)') as alias, Book.value('title[1]','NVARCHAR(100)') as title, Book.value('description[1]','NVARCHAR(100)') as description FROM @XML.nodes('application')Catalog(Book) )
    insert into dashboards (id, alias, title, description, version, application_id) (select * from #MyList as id, Book.value('alias[1]','NVARCHAR(100)') as alias, Book.value('title[1]','NVARCHAR(100)') as title, Book.value('description[1]','NVARCHAR(100)') as description, Book.value('version[1]','NVARCHAR(100)') as version, @applicationId as application_id FROM @XML.nodes('application/dashboards/dashboard')Catalog(Book))
RETURN;
END;

这是我得到的错误。

Msg 156, Level 15, State 1, Procedure ParseXML, Line 27 [Batch Start Line 5] Incorrect syntax near the keyword 'FROM'.

您可以使用此输入运行上述存储过程

--run
DECLARE @string NVARCHAR(MAX);
DECLARE @xmlstring XML;
SET @string = 
    '<application> <alias>appAlias1</alias> <title>appTitle1</title> <description>appDesc1</description> <dashboards class="list"> <dashboard> <id>dashboard1</id> <alias>alias1</alias> <version>version1</version> <title>title1</title> <description>desc1</description> </dashboard> <dashboard> <id>dashboard2</id> <alias>alias2</alias> <version>version2</version> <title>title2</title> <description>desc2</description> </dashboard> <dashboard> <id>dashboard3</id> <alias>alias3</alias> <version>version3</version> <title>title3</title> <description>desc3</description> </dashboard> <dashboard> <id>dashboard4</id> <alias>alias4</alias> <version>version4</version> <title>title4</title> <description>desc4</description> </dashboard> <dashboard> <id>dashboard5</id> <alias>alias5</alias> <version>version5</version> <title>title5</title> <description>desc5</description> </dashboard> </dashboards> </application>'  
SET @xmlstring = @string;
EXEC dbo.ParseXML @xmlstring;
SELECT @xmlstring;

请告诉我如何添加一列以及通过在存储过程中使用 xQuery 解析 XML 获得的其余值。

编辑:感谢@Mikael Eriksson 和@Roger Wolf 回答和审查我的程序。

只是为了完成答案，比方说，我想插入一个表，其中一列的值来 self 的 sql server 中已经存在的表，而其他列的值来自 XML，这怎么可能完毕。我试过类似的方法，但失败了。

insert into dashboards (id, alias, title, description, version, application_id) (select top 3 id from applications, select Retriever.value('alias[1]','NVARCHAR(100)') as alias, Retriever.value('title[1]','NVARCHAR(100)') as title, Retriever.value('description[1]','NVARCHAR(100)') as description, Retriever.value('version[1]','NVARCHAR(100)') as version, @applicationId as application_id FROM @XML.nodes('application/dashboards/dashboard') TableAlias(Retriever))

也试过

insert into dashboards (id, alias, title, description, version, application_id) (select top 3 id from applications, Retriever.value('alias[1]','NVARCHAR(100)') as alias, Retriever.value('title[1]','NVARCHAR(100)') as title, Retriever.value('description[1]','NVARCHAR(100)') as description, Retriever.value('version[1]','NVARCHAR(100)') as version, @applicationId as application_id FROM @XML.nodes('application/dashboards/dashboard') TableAlias(Retriever))

都给我

Incorrect syntax near the keyword 'select'.

最佳答案

您可以在插入语句中直接使用 newid()。不需要循环。

这对您的程序来说已经足够了。

declare @applicationId NVARCHAR(36);
select @applicationId = NEWID();

insert into dashboards (id, alias, title, description, version, application_id) 
select newid() as id, 
       Book.value('alias[1]','NVARCHAR(100)') as alias, 
       Book.value('title[1]','NVARCHAR(100)') as title, 
       Book.value('description[1]','NVARCHAR(100)') as description, 
       Book.value('version[1]','NVARCHAR(100)') as version, 
       @applicationId as application_id 
from @XML.nodes('application/dashboards/dashboard') Catalog(Book)

关于sql-server - 在 MS SQL Server 的存储过程中使用 xQuery 时插入来自 XML 之外的值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/41605673/