August 21, 2009 at 8:53 am
Hello
I'm trying to read from a table that contains an XML column:
DECLARE @xmlDoc XML, @handle INT, @FID INT
SET @FID = 25
SET @xmlDoc = (SELECT Faktura FROM Faktura.XMLFaktura where faktura_id = @FID)
EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc
Now I need to insert som data into another table as regular columns, the problem is that the target tables primary key column is not to be inserted from the XML but it should be the same as the @FID variable.
The primary key column is named faktura_id.
This is what I have:
INSERT INTO Faktura.TempFaktura
SELECT * FROM OPENXML (@handle, 'FAKTURA', 2)
WITH (lev_namnVARCHAR(50) 'FÖRETAG/NAMN',
lev_orgnrCHAR(15) 'FÖRETAG/ORGNR',
lev_postgiroCHAR(10) 'FÖRETAG/POSTGIRO',
kopareCHAR(10) 'KÖPARE/FÖRETAGSNAMN',
bestallareVARCHAR(50) 'KÖPARE/BESTÄLLARE',
fakturanrINT 'FAKTURA_DETALJER/FAKTURA_NR',
datumSMALLDATETIME 'FAKTURA_DETALJER/DATUM',
totalsumCHAR(20) 'FAKTURA_DETALJER/TOTAL_SUMMA',
totalmomsCHAR(20) 'FAKTURA_DETALJER/TOTAL_MOMS'
)
EXEC sp_xml_removedocument @handle
How can I insert the @FID at the same time into the faktura_id column?
Thanks
August 21, 2009 at 12:47 pm
Using OPENXML in this way means you can only do inserts one row at a time, and restricts your options.
You should look over the solid advice from Jacob Sebastian here on how to do this in a set fashion:
http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/e24bedf7-ad70-4083-8149-a00b740df950
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply