Dynamic SQL for variable TableName with OpenXML

  • Here is my current code. Because I have the table name as a variable, it is my understanding that I need to use Dynamic SQL. But whenever I use dynamic SQL it causes a problem with the OpenXML statement. Can anyone shed some light on how this code should be written?

    SELECT @SQL = 'DECLARE @strXML varchar(8000)

    SELECT @strXML = TranRq

    FROM ' + @BrSumDesc + '

    WHERE EJTranID = ''' + @ejtranno + ''' and lower(transactioncode) in (''tlrckmoneyord'')

    DECLARE @xmldocpointer int

    EXEC sp_xml_preparedocument @xmldocpointer OUTPUT, @strXML

    SELECT [' + @BRSumDesc + '].EJTranID AS EJTranNo,

    Rtrim(ProcDateM) + ''/'' + Rtrim(ProcDateD) + ''/'' + ProcDateY as ProcDate

    FROM OpenXml(@xmldocpointer, ''/IFX/MonSvcRq/MoneyOrdSellAddRq/TellerData'', 2)

    WITH (ProcDateM Char(2) ''./ProcDate/Month'',

    ProcDateD Char(2) ''./ProcDate/Day'',

    ProcDateY Char(4) ''./ProcDate/Year''

    )

    join [' + @BrSumDesc + '] on EJTranID = ''' + @ejtranno + '''

    EXEC sp_xml_removedocument @xmldocpointer'

    PRINT @SQL

    EXEC (@SQL)

  • Has the XML you are trying to work with got a namespace defined? If it has you have to set up the namespace in the sp_xml_preparedocument arguments.

    My first and only foray into XML taught me this after much hair extraction!

    Tim

  • Brooke -

    could you attach a sample of the input file and what you're trying to get out of it. It would help to decipher what it is you're trying to get out of it.

    also - what's generating the XML input? Do you have any control over it?

    ----------------------------------------------------------------------------------
    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 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply