Import XML returns 0 rows affected failing

Question

Post reply

Import XML returns 0 rows affected failing

Last

Old Hand

Points: 331
More actions
November 29, 2013 at 3:24 am

#301911

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>
Here is my SQL import script
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)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Last Old Hand Points: 331 More actions · Answer 1

The data is importing as the last line had to be changed to

From @x.nodes('/status/CustomerDeposits/*') PropertyFeed(P)

Now my issue is how can I get my sql script to reconcile on the id column because if i reimport the data list it duplicates the ones that are currently there and add new ones?

liteswitch SSCommitted Points: 1824 More actions · Answer 2

Import the XML into a temporary staging table, then issue a merge command between that and the live one, so you're updating existing data and only adding in new rows.