SQL Server Forums
Profile | Register | 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
 New Topic  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
52325 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
52325 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
52325 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  
 New 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.11 seconds. Powered By: Snitz Forums 2000