I am facing the same problem.I need to load the data from a xml source.My xml is as follows:-
<?xml version="1.0"?>
<Products>
<Product id="1">
<ProductName>Pr1</ProductName>
<Quantity>5</Quantity>
<ProductType typeid="1">Household</ProductType>
<Price currency="Rupees">15.00</Price>
</Product>
<Product id="2">
<ProductName>Pr2</ProductName>
<Quantity>10</Quantity>
<ProductType typeid="2">Disinfectants</ProductType>
<Price currency="Yen">25.00</Price>
</Product>
<Product id="3">
<ProductName>Pr3</ProductName>
<Quantity>15</Quantity>
<ProductType typeid="3">Wood</ProductType>
<Price currency="Rupees">56.00</Price>
</Product>
<Product id="4">
<ProductName>Pr4</ProductName>
<Quantity>52</Quantity>
<ProductType typeid="1">Household</ProductType>
<Price currency="Dollars">53.00</Price>
</Product>
<Product id="5">
<ProductName>Pr5</ProductName>
<Quantity>54</Quantity>
<ProductType typeid="1">Household</ProductType>
<Price currency="Dime">5.00</Price>
</Product>
<Product id="6">
<ProductName>Pr6</ProductName>
<Quantity>9</Quantity>
<ProductType typeid="2">Disinfectants</ProductType>
<Price currency="Dollars">5.00</Price>
</Product>
<Product id="7">
<ProductName>Pr7</ProductName>
<Quantity>95</Quantity>
<ProductType typeid="3">Wood</ProductType>
<Price currency="Yen">52.00</Price>
</Product>
<Product id="8">
<ProductName>Pr8</ProductName>
<Quantity>54</Quantity>
<ProductType typeid="2">Disinfectants</ProductType>
<Price currency="Dollar">52.00</Price>
</Product>
<Product id="9">
<ProductName>Pr9</ProductName>
<Quantity>22</Quantity>
<ProductType typeid="3">Wood</ProductType>
<Price currency="Dime">67.00</Price>
</Product>
<Product id="10">
<ProductName>Pr10</ProductName>
<Quantity>54</Quantity>
<ProductType typeid="2">Disinfectants</ProductType>
<Price currency="Dollar">52.00</Price>
</Product>
</Products>
I am getting three tables from teh XML source once placed in SSIS.
I created a products table having id as teh primary key.
CREATE TABLE [dbo].[products](
[id] [tinyint] NOT NULL,
[ProductName] [nvarchar](100) NULL,
[Quantity] [tinyint] NULL,
CONSTRAINT [pk1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
This is teh script for Priceinfo table
CREATE TABLE [dbo].[Priceinfo](
[currency] [nvarchar](255) NULL,
[text] [decimal](28, 10) NULL,
[id] [tinyint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Priceinfo] WITH CHECK ADD CONSTRAINT [fk1] FOREIGN KEY([id])
REFERENCES [dbo].[products] ([id])
GO
ALTER TABLE [dbo].[Priceinfo] CHECK CONSTRAINT [fk1]
GO
This is teh script for Productstype table
CREATE TABLE [dbo].[Producttype](
[typeid] [tinyint] NULL,
[text] [nvarchar](255) NULL,
[id] [tinyint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Producttype] WITH CHECK ADD CONSTRAINT [fk2] FOREIGN KEY([id])
REFERENCES [dbo].[products] ([id])
GO
ALTER TABLE [dbo].[Producttype] CHECK CONSTRAINT [fk2]
GO
The ID in teh priceinfo and products type table are not getting loaded.Please help.