Post reply

Parsing XML Document

  • September 5, 2007 at 5:30 pm

    #174503

    I am retrieving an XML Doc with the below layout.  I am trying to extract the Names along with their record number.

    <rootStart>

    <nodeData nodeName"result">Success</nodeData>

    <nodeData nodeName"return">

       <nodeData nodeName"recordCount">3</nodeData>

       <nodeData nodeName"record1">James</nodeData>

       <nodeData nodeName"record2">John</nodeData>

       <nodeData nodeName"record3">Bobby</nodeData>

    </nodeData>

    </rootStart>

    I am able to extract the attribute value (record1, record2, record3) into a table, but I cannot get the text to come along with it.  I am using:

        SELECT nodeName

          FROM OPENXML(@hdoc, '//nodeData',1)

                 WITH (

                       nodeData varchar(20) '//nodeData',

                       nodeName varchar(100) '@nodeName'

                       ) setNames

         WHERE nodeName >= 'record1'

           AND nodeName <= 'record999'

    I know there are some obvious syntax errors, but I have tried so many patterns that I'm not sure what to try next.  I appreciate any help!

  • September 6, 2007 at 7:31 am

    #731814 

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

    SELECT nodeData, nodeName
      FROM OPENXML(@hdoc, '//nodeData',1)
             WITH (
                   nodeData varchar(20) '.',
                   nodeName varchar(100) '@nodeName'
                   ) setNames
     WHERE nodeName >= 'record1'
       AND nodeName <= 'record999'

    This returns:

    nodeData nodeName

    -------------------- ----------------------------------------------------------------------------------------------------

    James record1

    John record2

    Bobby record3

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • September 6, 2007 at 9:06 am

    #731882

    Thank you, thank you!!

Viewing 3 posts - 1 through 2 (of 2 total)

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