Inserting into SQL using an OpenXml

Question

Hi I am trying to insert data into an SQL Server Database using an XML file which has some data as follows.I was able to do the attribute mapping in OPENXML.If i try to pass the XML as elements instead of attributes i get an error regarding the null insertion.

Following is my XML FILE (containg attributes)

<NewDataSet>
  <SampleDataTable id="20" Name="as" Address="aaa" Email="aa" Mobile="123" />
</NewDataSet>

I am successful using the above format.If i use the below format i face errors

<Customer>
  <Id>20</Id>
  <Name>Cn</Name>
  <Address>Pa</Address>
  <Email>bnso@gmail.com</Email>
  <Mobile>12345513213</Mobile>
</Customer>

This is my openXML in SQL

 insert into @tempTable
    select * from openxml (@xmlHandle,'ROOT/Customer/',1)
    with (Cust_id int '@id',
          Customer_Name varchar(30) '@Name',
          Address varchar(30) '@Address',
          Email_id varchar(30) '@Email',
          Mobile_no bigint '@Mobile'
          )

    Insert into Test.dbo.tblCustomers (Cust_id,Customer_Name,Address,Email,Mobile_No) (select * from @tempTable)

please help

Answer 1

It's because you're trying to fetch data as attributes, but int your xml data is inside elements. Try this:

insert into @tempTable
select *
from openxml (@xmlHandle,'ROOT/Customer/',1)
with (Cust_id int '@id',
      Customer_Name varchar(30) 'Name[1]',
      Address varchar(30) 'Address[1]',
      Email_id varchar(30) 'Email[1]',
      Mobile_no bigint 'Mobile[1]'
      )

Or you can do this without openxml:

select
    t.c.value('Name[1]', 'varchar(30)') as Name,
    t.c.value('Address[1]', 'varchar(30)') as Address,
    t.c.value('Email[1]', 'varchar(30)') as Email,
    t.c.value('Mobile[1]', 'bigint') as Mobile
from @Data.nodes('Customer') as t(c)

sql fiddle demo