Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Import XML returns 0 rows affected
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

last
Starting Member

25 Posts

Posted - 11/29/2013 :  05:20:35  Show Profile  Reply with Quote
Hi All

I am trying to do a bulk import of data from XML into sQL.
My query returns no errors but no data gets imported.
Here is my XML
?xml version="1.0" encoding="utf-8"?>

<status>
	<connection_status>successful</connection_status>
	<operation_status>successful</operation_status>
    <CustomerDeposits>
   	  <data_0>
   	  	<id>336</id>
        <customerId>111</customerId>
        <campaignId>0</campaignId>
        <type>deposit</type>
        <paymentMethod>Bonus</paymentMethod>
        <bankName></bankName>
        <bankNumber></bankNumber>
        <accountNumber></accountNumber>
        <branchNumber></branchNumber>
        <confirmationCode></confirmationCode>
        <iban></iban>  
        <clearedBy>AllCharge</clearedBy>
        <amount>20000.00</amount>
        <status>approved</status>  
        <transactionID>5b21a7688a2e301f2ab839817376963f</transactionID>
        <requestTime>2013-04-25 21:26:00</requestTime> 
        <confirmTime>2013-04-25 21:26:00</confirmTime>
        <requestTimeFormatted>PM 09:26 25/04/13</requestTimeFormatted>
        <confirmTimeFormatted>PM 09:26 25/04/13</confirmTimeFormatted>
        <IPAddress>64.148.233.214</IPAddress>
        <currency>USD</currency>
        </data_0>
        <data_1>
        <id>536</id>
        <customerId>111</customerId>
        <campaignId>0</campaignId>
        <type>deposit</type>
        <paymentMethod>Bonus</paymentMethod> 
        <bankName></bankName> 
        <bankNumber></bankNumber>
        <accountNumber></accountNumber>
        <branchNumber></branchNumber>
        <confirmationCode></confirmationCode>
        <iban></iban>
        <clearedBy>AllCharge</clearedBy>
        <amount>50000.00</amount>
        <status>approved</status>
<transactionID>a43c11963e18100c591c384282856a1a</transactionID>
        <requestTime>2013-07-25 00:35:00</requestTime>
        <confirmTime>2013-07-25 00:35:00</confirmTime>
        <requestTimeFormatted>AM 12:35 25/07/13</requestTimeFormatted>
        <confirmTimeFormatted>AM 12:35 25/07/13</confirmTimeFormatted>
        <IPAddress>64.148.233.214</IPAddress>
        <currency>USD</currency>
       </data_1>
       </CustomerDeposits>
      </status>


And here is my SQL code
Declare @xml XML

Select @xml =
CONVERT(XML,bulkcolumn,2) FROM OPENROWSET(BULK 'N:\CitiTrader\bb\Cody2.xml',SINGLE_BLOB) AS X

SET ARITHABORT ON

Insert into [CustomerDeposits]
(
id,customerId,campaignId,[type],paymentMethodBonus,paymentMethod,bankName,bankNumber,accountNumber,branchNumber,confirmationCode,iban,clearedBy,amount,[status],transactionID,requestTime,confirmTime,requestTimeFormatted,confirmTimeFormatted,IPAddress,currency
)

Select
P.value('id[1]','int') AS Id,
P.value('customerId[1]','int') AS CustomerId,
P.value('campaignId[1]','VARCHAR(50)') AS CampaignId,
P.value('type[1]','VARCHAR(50)') AS type,
P.value('paymentMethodBonus[1]','VARCHAR(50)') AS PaymentMethodBonus,
P.value('paymentMethod[1]','VARCHAR(50)') AS PaymentMethod,
P.value('bankName[1]','VARCHAR(50)') AS bankNamebankName,
P.value('bankNumber[1]','VARCHAR(50)') AS bankNumber,
P.value('accountNumber[1]','VARCHAR(50)') AS accountNumber,
P.value('branchNumber[1]','VARCHAR(50)') AS branchNumber,
P.value('confirmationCode[1]','VARCHAR(50)') AS confirmationCode,
P.value('iban[1]','VARCHAR(50)') AS iban,
P.value('clearedBy[1]','VARCHAR(50)') AS clearedBy,
P.value('amount[1]','VARCHAR(50)') AS amount,
P.value('status[1]','VARCHAR(50)') AS status,
P.value('transactionID[1]','VARCHAR(50)') AS transactionID,
P.value('requestTime[1]','VARCHAR(50)') AS requestTime,
P.value('confirmTime[1]','VARCHAR(50)') AS confirmTime,
P.value('requestTimeFormatted[1]','VARCHAR(50)') AS requestTimeFormatted,
P.value('confirmTimeFormatted[1]','VARCHAR(50)') AS confirmTimeFormatted,
P.value('IPAddress[1]','VARCHAR(18)') AS IPAddress,
P.value('currency[1]','VARCHAR(50)') AS currency
From @xml.nodes('/status/connection_status/operation_status/CustomerDeposits') PropertyFeed(P)

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/29/2013 :  05:52:51  Show Profile  Reply with Quote
see illustration below

declare @x xml='?xml version="1.0" encoding="utf-8"?>
<status>
	<connection_status>successful</connection_status>
	<operation_status>successful</operation_status>
    <CustomerDeposits>
   	  <data_0>
   	  	<id>336</id>
        <customerId>111</customerId>
        <campaignId>0</campaignId>
        <type>deposit</type>
        <paymentMethod>Bonus</paymentMethod>
        <bankName></bankName>
        <bankNumber></bankNumber>
        <accountNumber></accountNumber>
        <branchNumber></branchNumber>
        <confirmationCode></confirmationCode>
        <iban></iban>  
        <clearedBy>AllCharge</clearedBy>
        <amount>20000.00</amount>
        <status>approved</status>  
        <transactionID>5b21a7688a2e301f2ab839817376963f</transactionID>
        <requestTime>2013-04-25 21:26:00</requestTime> 
        <confirmTime>2013-04-25 21:26:00</confirmTime>
        <requestTimeFormatted>PM 09:26 25/04/13</requestTimeFormatted>
        <confirmTimeFormatted>PM 09:26 25/04/13</confirmTimeFormatted>
        <IPAddress>64.148.233.214</IPAddress>
        <currency>USD</currency>
        </data_0>
        <data_1>
        <id>536</id>
        <customerId>111</customerId>
        <campaignId>0</campaignId>
        <type>deposit</type>
        <paymentMethod>Bonus</paymentMethod> 
        <bankName></bankName> 
        <bankNumber></bankNumber>
        <accountNumber></accountNumber>
        <branchNumber></branchNumber>
        <confirmationCode></confirmationCode>
        <iban></iban>
        <clearedBy>AllCharge</clearedBy>
        <amount>50000.00</amount>
        <status>approved</status>
<transactionID>a43c11963e18100c591c384282856a1a</transactionID>
        <requestTime>2013-07-25 00:35:00</requestTime>
        <confirmTime>2013-07-25 00:35:00</confirmTime>
        <requestTimeFormatted>AM 12:35 25/07/13</requestTimeFormatted>
        <confirmTimeFormatted>AM 12:35 25/07/13</confirmTimeFormatted>
        <IPAddress>64.148.233.214</IPAddress>
        <currency>USD</currency>
       </data_1>
       </CustomerDeposits>
      </status>'

	  Select
P.value('id[1]','int') AS Id,
P.value('customerId[1]','int') AS CustomerId,
P.value('campaignId[1]','VARCHAR(50)') AS CampaignId,
P.value('type[1]','VARCHAR(50)') AS type,
P.value('paymentMethodBonus[1]','VARCHAR(50)') AS PaymentMethodBonus,
P.value('paymentMethod[1]','VARCHAR(50)') AS PaymentMethod,
P.value('bankName[1]','VARCHAR(50)') AS bankNamebankName,
P.value('bankNumber[1]','VARCHAR(50)') AS bankNumber,
P.value('accountNumber[1]','VARCHAR(50)') AS accountNumber,
P.value('branchNumber[1]','VARCHAR(50)') AS branchNumber,
P.value('confirmationCode[1]','VARCHAR(50)') AS confirmationCode,
P.value('iban[1]','VARCHAR(50)') AS iban,
P.value('clearedBy[1]','VARCHAR(50)') AS clearedBy,
P.value('amount[1]','VARCHAR(50)') AS amount,
P.value('status[1]','VARCHAR(50)') AS status,
P.value('transactionID[1]','VARCHAR(50)') AS transactionID,
P.value('requestTime[1]','VARCHAR(50)') AS requestTime,
P.value('confirmTime[1]','VARCHAR(50)') AS confirmTime,
P.value('requestTimeFormatted[1]','VARCHAR(50)') AS requestTimeFormatted,
P.value('confirmTimeFormatted[1]','VARCHAR(50)') AS confirmTimeFormatted,
P.value('IPAddress[1]','VARCHAR(18)') AS IPAddress,
P.value('currency[1]','VARCHAR(50)') AS currency
From @x.nodes('/status/CustomerDeposits/*') PropertyFeed(P)


Id	CustomerId	CampaignId	type	PaymentMethodBonus	PaymentMethod	bankNamebankName	bankNumber	accountNumber	branchNumber	confirmationCode	iban	clearedBy	amount	status	transactionID	requestTime	confirmTime	requestTimeFormatted	confirmTimeFormatted	IPAddress	currency
336	111	0	deposit	NULL	Bonus							AllCharge	20000.00	approved	5b21a7688a2e301f2ab839817376963f	2013-04-25 21:26:00	2013-04-25 21:26:00	PM 09:26 25/04/13	PM 09:26 25/04/13	64.148.233.214	USD
536	111	0	deposit	NULL	Bonus							AllCharge	50000.00	approved	a43c11963e18100c591c384282856a1a	2013-07-25 00:35:00	2013-07-25 00:35:00	AM 12:35 25/07/13	AM 12:35 25/07/13	64.148.233.214	USD



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

last
Starting Member

25 Posts

Posted - 11/29/2013 :  06:36:42  Show Profile  Reply with Quote
Thank you that sorted me out.
One question though I noticed that if I run the import again the data gets duplicated .How can I get my query to reconcile on the id column
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/29/2013 :  06:42:53  Show Profile  Reply with Quote

Insert into [CustomerDeposits]
(
id,customerId,campaignId,[type],paymentMethodBonus,paymentMethod,bankName,bankNumber,accountNumber,branchNumber,confirmationCode,iban,clearedBy,amount,[status],transactionID,requestTime,confirmTime,requestTimeFormatted,confirmTimeFormatted,IPAddress,currency
)
select *
from
(
Select
P.value('id[1]','int') AS Id,
P.value('customerId[1]','int') AS CustomerId,
P.value('campaignId[1]','VARCHAR(50)') AS CampaignId,
P.value('type[1]','VARCHAR(50)') AS type,
P.value('paymentMethodBonus[1]','VARCHAR(50)') AS PaymentMethodBonus,
P.value('paymentMethod[1]','VARCHAR(50)') AS PaymentMethod,
P.value('bankName[1]','VARCHAR(50)') AS bankNamebankName,
P.value('bankNumber[1]','VARCHAR(50)') AS bankNumber,
P.value('accountNumber[1]','VARCHAR(50)') AS accountNumber,
P.value('branchNumber[1]','VARCHAR(50)') AS branchNumber,
P.value('confirmationCode[1]','VARCHAR(50)') AS confirmationCode,
P.value('iban[1]','VARCHAR(50)') AS iban,
P.value('clearedBy[1]','VARCHAR(50)') AS clearedBy,
P.value('amount[1]','VARCHAR(50)') AS amount,
P.value('status[1]','VARCHAR(50)') AS status,
P.value('transactionID[1]','VARCHAR(50)') AS transactionID,
P.value('requestTime[1]','VARCHAR(50)') AS requestTime,
P.value('confirmTime[1]','VARCHAR(50)') AS confirmTime,
P.value('requestTimeFormatted[1]','VARCHAR(50)') AS requestTimeFormatted,
P.value('confirmTimeFormatted[1]','VARCHAR(50)') AS confirmTimeFormatted,
P.value('IPAddress[1]','VARCHAR(18)') AS IPAddress,
P.value('currency[1]','VARCHAR(50)') AS currency
From @x.nodes('/status/CustomerDeposits/*') PropertyFeed(P)
)t
WHERE NOT EXISTS (SELECT 1 FROM CustomerDeposits WHERE id = t.id)

do you want existing data to be modified also for matching id values? then you need an update too.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

last
Starting Member

25 Posts

Posted - 11/29/2013 :  06:49:08  Show Profile  Reply with Quote
Thank you so much.That is sorted. No i wont be adding an update statement as the data should never be modified. Once again thank you for your quick help .
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/30/2013 :  01:30:03  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000