Import XML returns 0 rows affected

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

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Import XML returns 0 rows affected

Author

Topic

last
Starting Member

25 Posts

Posted - 2013-11-29 : 05:20:35

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

52326 Posts

Posted - 2013-11-29 : 05:52:51

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

last
Starting Member

25 Posts

Posted - 2013-11-29 : 06:36:42

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-29 : 06:42:53

[code]
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)
[/code]
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

last
Starting Member

25 Posts

Posted - 2013-11-29 : 06:49:08

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 .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 01:30:03

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources