How to retrieve data from this XML

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

Answer 1

Here is an example for a bulk import :http://msdn.microsoft.com/en-us/library/ms191184.aspx

Here is another: http://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

---

Andy Tauber
Data Architect
The Vancouver Clinic
Website | LinkedIn

Answer 2

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

Answer 3

Thank you  Visakh

Thank  You Andy Tauber