How to import Excel with XML

• itmasterw 60042

  SSCrazy

  Points: 2858

  More actions

  January 29, 2020 at 12:14 pm

  #3721151

  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: 148809

  More actions

  January 29, 2020 at 12:31 pm

  #3721160

  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: 2858

  More actions

  January 29, 2020 at 12:59 pm

  #3721167

  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