Load XML Element to the database
-
I have assigned a task to load the XML Element into the database as I am new to SSIS and not sure how to load the xml element to the database. I tried using XML source and it gives me a each element as a table. But it is not my requirement.
Sample XML File.
<?xml version="1.0" encoding="utf-8"?>
<file>
<count>1159</count>
<fiid>46</fiid>
<partnerid>1</partnerid>
<productid>1</productid>
<timestamp>2011-02-16T12:05:35.8107751Z</timestamp>
<size>33</size>
<images>
<image>
<CardImageId>3roqlsdm2</CardImageId>
<DesignCreatedTimeStamp>2010-04-13T14:47:20.81</DesignCreatedTimeStamp>
<MessageID>efdb6481-9a1d-4883-859f-d5f07ed28848</MessageID>
<DesignState>6</DesignState>
<RejectionReason />
<ImageType>Gallery</ImageType>
<TransactionApprovalState>0</TransactionApprovalState>
<DataFields>
<Data id="name" value="tom" />
<Data id="6digits" value="234234" />
<Data id="a" value="dfgdfgdfgdfg" />
<Data id="b" value="c" />
<Data id="c" value="" />
<Data id="d" value="234234" />
<Data id="e" value="False" />
</DataFields>
</image>
<image>
<CardImageId>3vrdp4ec4</CardImageId>
<DesignCreatedTimeStamp>2010-09-14T10:11:29.967</DesignCreatedTimeStamp>
<MessageID>efdb6481-9a1d-4883-859f-d5f07ed28848</MessageID>
<DesignState>6</DesignState>
<RejectionReason />
<ImageType>Gallery</ImageType>
<TransactionApprovalState>0</TransactionApprovalState>
<DataFields>
<Data id="name" value="" />
<Data id="6digits" value="123123" />
<Data id="a" value="" />
<Data id="b" value="c" />
<Data id="c" value="" />
<Data id="d" value="" />
<Data id="e" value="False" />
</DataFields>
</image>
<image>
<CardImageId>3dw4pauj3</CardImageId>
<DesignCreatedTimeStamp>2010-09-14T10:14:06.137</DesignCreatedTimeStamp>
<MessageID>efdb6481-9a1d-4883-859f-d5f07ed28848</MessageID>
<DesignState>6</DesignState>
<RejectionReason />
<ImageType>Gallery</ImageType>
<TransactionApprovalState>0</TransactionApprovalState>
<DataFields>
<Data id="name" value="" />
<Data id="6digits" value="121233" />
<Data id="a" value="" />
<Data id="b" value="c" />
<Data id="c" value="" />
<Data id="d" value="" />
<Data id="e" value="False" />
</DataFields>
</image>
</images>
</file>
Data should be like below...
Messageid MessageElement
efdb6481-9a1d-4883-859f-d5f07ed28848 <image> <CardImageId>3roqlsdm2</CardImageId> DesignCreatedTimeStamp>2010-04-13T14:47:20.81</DesignCreatedTimeStamp> <MessageID>efdb6481-9a1d-4883-859f-d5f07ed28848</MessageID>
<DesignState>6</DesignState>
<RejectionReason />
<ImageType>Gallery</ImageType>
<TransactionApprovalState>0</TransactionApprovalState>
<DataFields>
<Data id="name" value="tom" />
<Data id="6digits" value="234234" />
<Data id="a" value="dfgdfgdfgdfg" />
<Data id="b" value="c" />
<Data id="c" value="" />
<Data id="d" value="234234" />
<Data id="e" value="False" />
</DataFields>
</image>
MessageID = from the MessageID tag and Message element = Whole xml content of the Image element
Please let me know what are the steps i need to follow to complete my task.
Thanks,
Karthik
-
I'd be inclined to step outside of SSIS and use an SQL Script Object to query that. OpenRowset, with the Bulk and Single_CLOB options, will pull XML files into SQL Server, and then a simple XQuery action, using the nodes function, will get you the XML of the node.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks for the prompt reply. Yes, we can achieve that in T-SQL however the requirement is to do only with SSIS package because there are other operations that we need to do after the import.
-
You have to transform your XML with XSLT, before loading it with the XML Source component. Check this post.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
This post gives an idea about picking the particular element in XML, how can i insert the each element in each row?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply