locked
Import returns 0 rows affected RRS feed

  • Question

  • User1487774783 posted

    Hi  All

     

    I am trying to import my xml document into the database. My query returns no error but it no data is imported please help

    This is my import code:

    Declare @xml XML
    
    Select @xml =
    CONVERT(XML,bulkcolumn,2) FROM OPENROWSET(BULK 'C:\deposits.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)

    Below is the 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>



      

     

    Friday, November 29, 2013 5:13 AM

Answers

  • User1508394307 posted

    The path

    From @xml.nodes('/status/connection_status/operation_status/CustomerDeposits') PropertyFeed(P)

    is wrong.

    Try

    From @xml.nodes('/status/CustomerDeposits/data_0') PropertyFeed(P)

    P.S.

    If you would need to select all items (data_0, data_1, etc) then use

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

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 29, 2013 7:33 AM

All replies

  • User1508394307 posted

    The path

    From @xml.nodes('/status/connection_status/operation_status/CustomerDeposits') PropertyFeed(P)

    is wrong.

    Try

    From @xml.nodes('/status/CustomerDeposits/data_0') PropertyFeed(P)

    P.S.

    If you would need to select all items (data_0, data_1, etc) then use

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

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 29, 2013 7:33 AM
  • User753101303 posted

    Hi,

    Isn't your node  /status/CustomerDeposits ? connection_status and operation_status seems to be at the same level directly under the status node ???

    Just try first to get the SELECT statement ok before using this in your insert (not sure how you'll handle the data_0 data_1, IMO nodes should have the same name here it mixes a tag name what appears to be a data such as an ordering number.

    Friday, November 29, 2013 7:34 AM