SQL Server Performance

OPENXML in Stored Procedure

Discussion in 'General Developer Questions' started by paandi, May 12, 2003.

  1. paandi New Member

    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.
  2. trifunk New Member

    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

Share This Page