insert xml record plus date into a time table

Asked

Viewed 42 times

-1

I wanted to make this select but I can’t 

>     DECLARE @XML XML
>     SET @XML = 
>     '<rows> <row> <tracking> JU148345357BR</tracking> </row> <row> <tracking> PM318855649BR</tracking> </row></rows>'
>     insert into #tracking3 (tracking, data) values (SELECT Tbl.Col.value('tracking[1]', 'varchar(13)') as tracking FROM  
> @xml.nodes('//row') Tbl(Col), getDate()
sql database sql-server xml sql-insert

1 answer

0


Here is the solution. But the temporary table needs to be created, you know how to create?

DECLARE @hDoc int

DECLARE @XML XML
        SET @XML = '<rows> <row> <tracking> JU148345357BR</tracking> </row> <row> <tracking> PM318855649BR</tracking> </row></rows>'
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML 
        INSERT INTO #tracking3 
        (tracking, data)
        SELECT 
            XMLTable.tracking,
            GETDATE()
        FROM OPENXML(@hDoc, 'rows/row',2)   
        WITH (
             tracking  varchar(max)
            ) XMLTable
    EXEC sp_xml_removedocument @hDoc

Browser other questions tagged sql database sql-server xml sql-insert

You are not signed in. Login or sign up in order to post.