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'.

score 3 · Accepted Answer · answered Jan 12 '17 at 06:33

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)

score 2 · Answer 2 · answered Jan 12 '17 at 06:36

TL;DR - This is not how sets are joined in SQL.

The following code corrects this and some other mistakes and inefficiencies. The comments explain my changes:

CREATE PROCEDURE dbo.ParseXML
   @XML XML
AS

-- You can save a bit on initialisation this way
DECLARE @applicationId uniqueidentifier = newid(),
    @dashboardcount INT, @dashboardscount INT, @applicationcount INT;

-- SELECT is not necessary, SET is enough and it is faster
set @dashboardcount = @XML.value('count(/application/dashboards/dashboard)', 'int');

drop table if exists #MyList;
create table #MyList (
    -- Should the data type be a uniqueidentifier, instead?
    id nvarchar( 36 ) not null
);

-- "Catalog" is a reserved keyword, it is better to avoid using it as an alias
/*
insert into applications (id, alias, title, description)
select @applicationId as id,
    c.b.value('alias[1]','NVARCHAR(100)') as alias,
    c.b.value('title[1]','NVARCHAR(100)') as title,
    c.b.value('description[1]','NVARCHAR(100)') as description
FROM @XML.nodes('/application') c(b);
*/

insert into dbo.dashboards (id, alias, title, [description], [version], application_id)
-- This is how you catch inserted values when they are generated on the fly
output inserted.id into #MyList (id)
select newid() as Id, -- No need to pre-cache
    c.b.value('alias[1]','NVARCHAR(100)') as alias,
    c.b.value('title[1]','NVARCHAR(100)') as title,
    c.b.value('description[1]','NVARCHAR(100)') as [description],
    c.b.value('version[1]','NVARCHAR(100)') as [version],
    @applicationId as application_id
FROM @XML.nodes('application/dashboards/dashboard') c(b);

-- Now you can return the list of inserted GUIDs
select * from #MyList;

RETURN;
go

I decided to keep your #MyList temp table untouched, because it can be a simplified example, and you might need the inserted values after that. However, if storing pre-generated GUIDs was its only purpose, you can safely get rid of it altogether.

thanks for answering and reviewing. I have incorporated some of the comments from your review, regarding not using Catalog. Please see the edit above, I had one more follow up question on that one. — tubby, Jan 12 '17 at 19:13
@PepperBoy, haven't you noticed that in my answer? When you do `insert into ... select ...`, you don't put the `select` part into brackets. Brackets are only necessary when you use the `insert into ... values (...)` syntax. See https://msdn.microsoft.com/en-us/library/ms174335.aspx for complete syntax. — Roger Wolf, Jan 13 '17 at 06:47

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

2 Answers2