Post reply

how to loop through all child nodes in XML for import process

  • November 21, 2007 at 7:38 am

    #181315

    i've loaded an XML doc into an XML column type and now need to loop through all child nodes and import to another sql table. here's a sample of the script:

    DECLARE @XMLDocXML

    select @XMLDoc = xmlDoc from xmlData

    insert into tblPools

    SELECT

    C.value('(AccountInformation/AccountNumber)[1]','varChar(100)') as AccountNumber

    FROM @XMLDoc.nodes('/Report/PoolItems/PoolInfo') T(C)

    the AccountInformation/AccountNumber nodes can repeat any number of times. in my sample code above, i'm only grabbing the first instance ([1]).

    how can i drill down until i've exhausted all child nodes?

    tks in advance

  • November 21, 2007 at 11:26 am

    #753195

    you want to look up the CROSS Apply example from the "XML Best Practices for SQL Server 2005". You can do ALL of that in one shot (no variable, no loop)

    Link is here: XML Best Practices for SQL Server 2005

    Your example should look something like

    insert into tblPools

    select

    C.value('(AccountInformation/AccountNumber)','varChar(100)') as AccountNumber

    from xmlData CROSS APPLY Xmldoc.nodes('/Report/PoolItems/PoolInfo') T(C)

    ----------------------------------------------------------------------------------
    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?

  • November 22, 2007 at 6:24 am

    #753389

    great. tks for the help!

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

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