Solved

How to import from XML into Sql Server table

Posted on 2014-11-07
3
173 Views
Last Modified: 2014-11-08
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.
0
Comment
Question by:tesupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 34

Accepted Solution

by:
ste5an earned 500 total points
ID: 40429029
Cause there are two problems:

1. Your XML has a default namespace.
2. Your XPath expression is not correct. Where did you get (Column[@Name="Account"]/text())[1] from??

DECLARE @xml XML = N'
<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>';

WITH XMLNAMESPACES ( DEFAULT 'http://GLTransaction.xsd' )
	SELECT	c.value('Account[1]', 'VARCHAR(25)') Account,
		c.value('Amount[1]', 'NUMERIC(14,2)') Amount,
		c.value('Description[1]', 'VARCHAR(100)') [Description],
		c.value('DisplayOrder[1]', 'INT') DisplayOrder,
		c.value('IsSummaryAccount[1]', 'VARCHAR(5)') IsSummaryAcct
	FROM	@xml.nodes('/IntegrationData/GLTransaction/GLTransactionDistribution') AS c(c);

Open in new window

0
 

Author Comment

by:tesupport
ID: 40429295
I got that from an example, which after playing with it I took that part out.  Thank you so much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question