Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic SQL for variable TableName with OpenXML Expand / Collapse
Author
Message
Posted Friday, February 8, 2008 2:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 7, 2008 1:53 PM
Points: 4, Visits: 32
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)
Post #453497
Posted Monday, February 11, 2008 10:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:24 AM
Points: 18, Visits: 171
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
Post #453959
Posted Monday, February 11, 2008 11:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 7,158, Visits: 15,268
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?
Post #453993
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse