How to import Excel with XML

  • itmasterw 60042

    SSCrazy

    Points: 2746

    Hi,

    I have an Excel sheet that I have to import into SQL Server.

    It just has 3 columns, with one that looks like XML to me. Now I will admit I am not an expert with XML.

    The columns are something like this:

    Note: The column with  Acct Info below has what I believe is XML

    Question: How can I import this into SQL server.

    ID

    Account ID --> 1234     

    Act ID  --> 12456

    Acct Info -->

    <OrigOtherData>

    <Table>

    <Seller>The house</Seller>

    <Token>12345678</Token>

    <RefNo>401968058</RefNo>

    <PaymentHistoryDescription>good deal</PaymentHistoryDescription>

    <Product>Clorox</Product>

    <CreditLimit>123</CreditLimit>

    <MerchAPR>25.23</MerchAPR>

    <CashAPR>15.25</CashAPR>

    <Checking>M</Checking>

    <System>OSI</System>

    <OriginalLender>Bobs house</OriginalLender>

    </Table>

    </OrigOtherData>

     

    Thank you

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    What happens if you import it the same way you would any other file?  If you need the individual values in the XML separated into their own columns, import into a staging table and then split them out from there.  Search for "sql shred xml" if you don't know how to do that.

    John

  • itmasterw 60042

    SSCrazy

    Points: 2746

    Thanks for the reply.

    I am not sure what happened but yesterday it would not let me import it, but today on another machine it did.

    I will look into splitting it as you suggested.

    So I think I'm good here

    Thank you

     

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

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