Inserting a value that comes outside of XML while using xQuery in stored procedure in MS SQL Server

Question

I am parsing an xml input, using xQuery which helps parse xml files in a stored procedure like here, and then insert them into a table. I am able to insert the values from XML correctly into the table.

However, I want to insert a newly created UUID column as the first column of the table where I am doing the insertions, and only the rest of the columns' values need come from XML. Since xQuery inserts in a bulk, I have created a temporary table which holds the UUIDs, and am trying to insert it along with the rest of the values parsed from XML. However, it gives me an error.

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

This is the error I get.

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

You may run the above stored procedure using this input

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

Please tell me how I can add a column along with the rest of the values got by parsing XML using xQuery in a stored procedure.

---

EDIT : Thanks @Mikael Eriksson and @Roger Wolf for answering and reviewing my procedure as well.

Just to complete the answer, say, I want to insert into a table where the values for one column are to come from a table that is already present in my sql server, and for the others from XML, how can that be done. I tried something like this, but it fails.

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

Also tried

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

Both give me

Incorrect syntax near the keyword 'select'.

Answer 1

You can use newid() directly in your insert statement. There is no need for the loop.

This is enough for your procedure.

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)