Hi, I use OPENXML() functionality in a stored procedure to process a XML tree which is passed as parameter to the procedure. The information / values at the nodes are inserted / updated into multiples tables. This is how i get the values at any particular node: Select @EnquiryNumber = EnquiryNumber From OPENXML (@idoc, '/enquiry/header', 3) with ( EnquiryNumber numeric(9) '@EnquiryNumber' ) The above is to select the value of the EnquiryNumber attribute in the 'enquiry/header' node. At times i also have used the following to get the value of some other parent node, eventhough iam processing a child node. SELECT CLACODE,ITMCODE,NEED FROM OPENXML (@idoc, 'enquiry/header/ItemDetails/ITEM/Schedule/ScheduleDetails',3) with ( CLACODE numeric(9) '../../@CLACODE', ITMCODE numeric(9) '../../@ITMCODE', NEED char(10) '@NEED' ) In the above piece of code CLACODE is selected from a parent node. This is usually referred as NAVIGATING UP. In one of the sites i read that this navigating up is a performance bug (more time taken to process) in SQL 2000. This bug is due to navigating up the nodes in a with clause of OPENXML. I had used this approach at many places. Please update me whether this approach has got any performance bug. Thanks in advance. With Regards, Sridharan Jeganathan.
Yeah you're right it's a known bug and I think the general consensus is to avoid 'navigating up' as it is painfully slow. The alternative method is to use another OPENXML query and join to the other attribute/element. Something like this (I haven't tested this, it might need some work but I think you'll get the general idea) select t1.NEED, t2.CLACODE, t2.ITMCODE from openxml(@idoc, 'enquiry/header/ItemDetails/ITEM/Schedule/ScheduleDetails',3) with (NEED char(10) '@NEED') t1, openxml(@idoc, 'enquiry/header/ItemDetails/ITEM',3) with (CLACODE numeric(9) '../../@CLACODE', ITMCODE numeric(9) '../../@ITMCODE') t2 Cheers Shaun World Domination Through Superior Software
