Subquery returned more than 1 value with insert from XML

Question

This is a portion of a much larger stored procedure, but before I can get going I have to first parse XML, extract the attributes, and insert them into a temp table. Then I will use that table to perform functions later on.

When I try to insert into the temp table I am getting "Subquery returned more than 1 value". If my dummy XML contains one node it works fine, but if I add additional nodes issues come up.

Full SQL including XML to test:

DECLARE @MESSAGELIST XML
    set @MESSAGELIST = 
     '<object>
        <Record FirstName = ''Red'' LastName = ''Shark'' Email = ''D@d.com'' Date = ''01/01/2001'' ></Record>
        <Record FirstName = ''Jon'' LastName = ''Slow'' Email = ''D@d.com'' Date = ''01/01/2011'' ></Record>    
        <Record FirstName = ''Tyrone'' LastName = ''Lennystar'' Email = ''D@d33.com'' Date = ''01/11/2011'' ></Record>  
      </object>'

IF (OBJECT_ID('tempdb..#NHOMessagesTemp') IS NOT NULL) 
   DROP TABLE #NHOMessagesTemp

CREATE TABLE #NHOMessagesTemp 
(
    FirstName nvarchar(50), 
    LastName nvarchar(50), 
    Email nvarchar(100), 
    MessageDate datetime
)

INSERT INTO #NHOMessagesTemp    
VALUES (
    (SELECT I.FirstName.value('@FirstName', 'nvarchar(50)') 
     FROM @MESSAGELIST.nodes('/object/Record') AS I(FirstName)),
    (SELECT I.LastName.value('@LastName', 'nvarchar(50)') 
     FROM @MESSAGELIST.nodes('/object/Record') AS I(LastName)),
    (SELECT I.Email.value('@Email', 'nvarchar(100)') 
     FROM @MESSAGELIST.nodes('/object/Record') AS I(Email)),
    (SELECT I.[Date].value('@Date', 'datetime') 
     FROM @MESSAGELIST.nodes('/object/Record') AS I([Date]))
    )

SELECT * 
FROM #NHOMessagesTemp

I am sure the problem is with my

FROM @MESSAGELIST.nodes('/object/Record') AS...

but I don't know how to loop through the XML and get all of the values.

Thank you for your help!

Rather than using `VALUES` to insert, why don't you use a regular select statement? e.g. `SELECT A.B.value('@FirstName', 'nvarchar(50)') FirstName, A.B.value('@LastName', 'nvarchar(50)') LastName, A.B.value('@Email', 'nvarchar(100)') Email, A.B.value('@Date', 'datetime') [Date] FROM @MESSAGELIST.nodes('/object/Record') A(B)` — ZLK, Sep 29 '16 at 22:27
@ZLK that should be an answer I think... And here a related question for OP : [SQL Insert into … values ( SELECT … FROM … )](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) — har07, Sep 30 '16 at 01:24
@ZLK that is exactly what I ended up doing, I saw marc_s's response before yours. I was using the "values" keyword just based on previous experience. Thank you for your input! — DMcC, Oct 03 '16 at 14:03

score 1 · Accepted Answer · answered Sep 30 '16 at 05:06

Try this code to fetch the data from the XML :

SELECT
    FirstName = xc.value('@FirstName', 'varchar(50)'),
    LastName = xc.value('@LastName', 'varchar(50)'),
    Email = xc.value('@Email', 'varchar(50)'),
    Date = xc.value('@Date', 'varchar(50)')
FROM
    @MESSAGELIST.nodes('/object/Record') AS XT(XC)

and insert it into your temp table like so:

INSERT INTO #NHOMessagesTemp(FirstName, LastName, Email, Date)
    SELECT
        xc.value('@FirstName', 'varchar(50)'),
        xc.value('@LastName', 'varchar(50)'),
        xc.value('@Email', 'varchar(50)'),
        xc.value('@Date', 'varchar(50)')
    FROM
        @MESSAGELIST.nodes('/object/Record') AS XT(XC)

Subquery returned more than 1 value with insert from XML

1 Answers1