locked
How to retrieve data from this XML RRS feed

  • Question

  • Hi

    I am getting below XML file and I need to get data from the file into table in a database ( SQL SERVER ). Please kindly help to shred and load the data into a table 

    <claimInvoice xmlns="http://www.XYZ.com">
      <INum>INum1</INum>
      <dueAmount xmlns="">1</dueAmount>
      <Billadd xmlns="">Billadd1</Billadd>
      <remittance xmlns="">
        <RemCom>RemCom1</RemCom>
      </remittance>
      <summary xmlns="">
        <title>title1</title>   
        <accountAging>
              <totalDue>1</totalDue>
        </accountAging>
    </summary>
    </claimInvoice>

    How Can i get  data for the following :

    INum,
    dueDate,
    Billadd,
    RemCom,
    title,
    totalDue

    Thanks

    Kodi

    Wednesday, October 15, 2014 3:05 PM

Answers

  • see illustration below

    declare @x xml='<claimInvoice xmlns="http://www.XYZ.com">
      <INum>INum1</INum>
      <dueAmount xmlns="">1</dueAmount>
      <Billadd xmlns="">Billadd1</Billadd>
      <remittance xmlns="">
        <RemCom>RemCom1</RemCom>
      </remittance>
      <summary xmlns="">
        <title>title1</title>   
        <accountAging>
              <totalDue>1</totalDue>
        </accountAging>
    </summary>
    </claimInvoice>'
    
    ;WITH XMLNAMESPACES ('http://www.XYZ.com' AS def)
    
    SELECT t.u.value('def:INum[1]','varchar(50)') AS INum,
    t.u.value('dueAmount[1]','int') AS dueAmount,
    t.u.value('Billadd[1]','varchar(10)') AS Billadd,
    t.u.value('(remittance/RemCom)[1]','varchar(50)') AS RemCom,
    t.u.value('(summary/title)[1]','varchar(50)') AS title,
    t.u.value('(summary/accountAging/totalDue)[1]','int') AS totalDue
    FROM @x.nodes('/def:claimInvoice')t(u)
    


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by KODI_KODI Wednesday, October 15, 2014 4:48 PM
    Wednesday, October 15, 2014 4:36 PM
    • Marked as answer by KODI_KODI Wednesday, October 15, 2014 4:48 PM
    Wednesday, October 15, 2014 4:05 PM

All replies

    • Marked as answer by KODI_KODI Wednesday, October 15, 2014 4:48 PM
    Wednesday, October 15, 2014 4:05 PM
  • see illustration below

    declare @x xml='<claimInvoice xmlns="http://www.XYZ.com">
      <INum>INum1</INum>
      <dueAmount xmlns="">1</dueAmount>
      <Billadd xmlns="">Billadd1</Billadd>
      <remittance xmlns="">
        <RemCom>RemCom1</RemCom>
      </remittance>
      <summary xmlns="">
        <title>title1</title>   
        <accountAging>
              <totalDue>1</totalDue>
        </accountAging>
    </summary>
    </claimInvoice>'
    
    ;WITH XMLNAMESPACES ('http://www.XYZ.com' AS def)
    
    SELECT t.u.value('def:INum[1]','varchar(50)') AS INum,
    t.u.value('dueAmount[1]','int') AS dueAmount,
    t.u.value('Billadd[1]','varchar(10)') AS Billadd,
    t.u.value('(remittance/RemCom)[1]','varchar(50)') AS RemCom,
    t.u.value('(summary/title)[1]','varchar(50)') AS title,
    t.u.value('(summary/accountAging/totalDue)[1]','int') AS totalDue
    FROM @x.nodes('/def:claimInvoice')t(u)
    


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by KODI_KODI Wednesday, October 15, 2014 4:48 PM
    Wednesday, October 15, 2014 4:36 PM
  • Thank you  Visakh

    Thank  You Andy Tauber 

    Wednesday, October 15, 2014 4:48 PM