Link to home
Create AccountLog in
Avatar of tesupport
tesupportFlag for United States of America

asked on

How to import from XML into Sql Server table

I have an xml file that looks like this that I need to insert into a sql server 2008 table:

<?xml version="1.0" encoding="utf-8"?>
<IntegrationData xmlns="http://GLTransaction.xsd" xmlns:Vendor="http://C.com/Vendor.xsd">
  <ApplicationID>PayrollCS</ApplicationID>
  <DateTime>2014-07-21T10:59:06.9540775</DateTime>
  <UserID>PayrollCS</UserID>
  <GLTransaction>
    <Reference>113</Reference>
    <Date>2014-07-07</Date>
    <Description>Terry Eddington</Description>
    <PeriodDate>2014-07-07</PeriodDate>
    <Type>Check</Type>
    <JournalEntryInformation>
      <JournalEntrySubType>Regular</JournalEntrySubType>
    </JournalEntryInformation>
    <BankTransactionInformation>
      <BankAccount>test</BankAccount>
    </BankTransactionInformation>
    <Journal>General</Journal>
    <GLTransactionDistribution>
      <Account>999</Account>
      <Amount>0.00</Amount>
      <Description>Terry Eddington</Description>
      <DisplayOrder>1</DisplayOrder>
      <IsSummaryAccount>false</IsSummaryAccount>
    </GLTransactionDistribution>
    <GLTransactionDistribution>
      <Account>762</Account>
      <Amount>0.00</Amount>
      <Description>Terry Eddington</Description>
      <DisplayOrder>15</DisplayOrder>
      <IsSummaryAccount>false</IsSummaryAccount>
    </GLTransactionDistribution>
    <BankAccount>test</BankAccount>
  </GLTransaction>
  <GLTransaction>
    <Reference>119</Reference>
    <Date>2014-07-10</Date>
    <Description>Terry Eddington</Description>
    <PeriodDate>2014-07-10</PeriodDate>
    <Type>Check</Type>
    <JournalEntryInformation>
      <JournalEntrySubType>Regular</JournalEntrySubType>
    </JournalEntryInformation>
    <BankTransactionInformation>
      <BankAccount>test</BankAccount>
    </BankTransactionInformation>
    <Journal>General</Journal>
    <GLTransactionDistribution>
      <Account>751</Account>
      <Amount>2692.31</Amount>
      <Description>Terry Eddington</Description>
      <DisplayOrder>1</DisplayOrder>
      <IsSummaryAccount>false</IsSummaryAccount>
    </GLTransactionDistribution>
    <GLTransactionDistribution>
      <Account>999</Account>
      <Amount>0.00</Amount>
      <Description>Terry Eddington</Description>
      <DisplayOrder>2</DisplayOrder>
      <IsSummaryAccount>false</IsSummaryAccount>
    </GLTransactionDistribution>
  </GLTransaction>
</IntegrationData>

Open in new window

--I am only concerned with extracting five fields and created a table to hold those five fields.  I am

create table payroll
(Account [varchar](50),
Amount numeric(14,2) ,
[Description] [varchar](100) ,
DisplayOrder int ,
IsSummaryAcct varchar(5) )

--The below executes but nothing goes into the table
DECLARE @xml XML

SELECT @xml = x.y
FROM OPENROWSET( BULK 'F:\payroll.xml', SINGLE_CLOB ) x(y)

insert into payroll (Account, Amount, [Description], DisplayOrder, IsSummaryAcct)
SELECT
      c.c.value('(Column[@Name="Account"]/text())[1]', 'VARCHAR(25)') Account,
      c.c.value('(Column[@Name="Amount"]/text())[1]', 'Numeric(14,2)') Amount,
      c.c.value('(Column[@Name="Description"]/text())[1]', 'VARCHAR(100)') [Description],
      c.c.value('(Column[@Name="DisplayOrder"]/text())[1]', 'INT') DisplayOrder,
      c.c.value('(Column[@Name="IsSummaryAcct"]/text())[1]', 'VARCHAR(5)') IsSummaryAcct
FROM @xml.nodes('IntegrationData/GLTransaction/GLTransactionDistribution') AS c(c)

Any help or suggestions would be appreciated.  Thank you.
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of tesupport

ASKER

I got that from an example, which after playing with it I took that part out.  Thank you so much!