Home Forums Programming XML Inserting data using OPENXML RE: Inserting data using OPENXML

  • June 22, 2017 at 1:55 am

    #1947686

    thesqlcoder - Wednesday, June 21, 2017 6:12 AM

    Eirikur Eiriksson - Wednesday, June 21, 2017 5:25 AM

    thesqlcoder - Wednesday, June 21, 2017 5:10 AM

    Eirikur Eiriksson - Wednesday, June 21, 2017 3:58 AM

    This looks like relatively simple XML, you should just use XQuery to parse it
    😎

    Something like this:
    SELECT
      REPORT.DATA.query('.')
    FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)

    Thanks, Let me dig further from this solution.

    You'll find several examples on this forum, feel free to ping back if you have any problems.
    😎

    Thanks for your kind help, I'm looking for getting Insolvency(Data under <a:Insolvency>) data from above script but i can not.

    Here is a quick example on how to extract the insolvency data
    😎
    SELECT
      REPORT.DATA.value('@IDNumber','VARCHAR(50)')  AS IDNumber
     ,INSOLVENCY.DATA.value('(*:AccordingToLaw/text())[1]'    ,'VARCHAR(50)') AS AccordingToLaw
     ,INSOLVENCY.DATA.value('(*:CreditorTermDays/text())[1]'    ,'VARCHAR(50)') AS CreditorTermDays
     ,INSOLVENCY.DATA.value('(*:CreditorTermMonths/text())[1]'   ,'VARCHAR(50)') AS CreditorTermMonths
     ,INSOLVENCY.DATA.value('(*:CreditorTermWeeks/text())[1]'   ,'VARCHAR(50)') AS CreditorTermWeeks
     ,INSOLVENCY.DATA.value('(*:ProcessCharacter/text())[1]'    ,'VARCHAR(50)') AS ProcessCharacter
     ,INSOLVENCY.DATA.value('(*:ProcessEndDate/text())[1]'    ,'VARCHAR(50)') AS ProcessEndDate
     ,INSOLVENCY.DATA.value('(*:ProcessStartDate/text())[1]'    ,'VARCHAR(50)') AS ProcessStartDate
     ,INSOLVENCY.DATA.value('(*:StateOfInsolvencySolution/text())[1]' ,'VARCHAR(50)') AS StateOfInsolvencySolution
     ,INSOLVENCY.DATA.value('(*:TypeOfProcess/text())[1]'     ,'VARCHAR(50)') AS TypeOfProcess
     ,INSOLVENCY.DATA.query('.')                   AS INSOLVENCY_XML_DATA
    FROM @TXML.nodes('Reports/Report') AS REPORT(DATA)
    OUTER APPLY REPORT.DATA.nodes('*:CustomReport/*:Insolvencies/*:InsolvencyList/*:Insolvency') AS INSOLVENCY(DATA)