March 8, 2012 at 1:24 am
Hi,
I am trying to extract data from an XML file. eg
<root>
<album>
<producttype>music</producttype>
<barcode>111111111</barcode>
<productname>This is a test album</productname>
<tracks>
<track>
<cdno>1</cdno>
<trackname>my first single</trackname>
</track>
<track>
<cdno>2</cdno>
<trackname>My second track</trackname>
</track>
</tracks>
<notes>
<note>
<text>This album is home produced by myself and hopefully will be liked by many</text>
</note>
</notes>
</album>
Is there a way of extracting this in one go or dynamically as i have many XML files that has a samilar structure so my intention is too loop thru xml files with SSIS and process it one at a time based on the info i get perhaps from the XSD file eg.
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="root">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="album">
<xs:complexType>
<xs:sequence>
<xs:choice maxOccurs="unbounded">
<xs:element minOccurs="0" name="producttype" type="xs:string" />
<xs:element minOccurs="0" name="barcode" type="xs:unsignedLong" />
<xs:element minOccurs="0" name="barcodetype" type="xs:string" />
<xs:element minOccurs="0" name="origin" type="xs:string" />
<xs:element minOccurs="0" name="title" type="xs:string" />
<xs:element minOccurs="0" name="artist" type="xs:string" />
<xs:element minOccurs="0" name="altartist" type="xs:string" />
<xs:element minOccurs="0" name="composer" type="xs:string" />
<xs:element minOccurs="0" name="stock" type="xs:string" />
<xs:element minOccurs="0" name="coupling" type="xs:string" />
<xs:element minOccurs="0" name="source" type="xs:string" />
<xs:element minOccurs="0" name="label" type="xs:string" />
<xs:element minOccurs="0" name="media" type="xs:string" />
<xs:element minOccurs="0" name="mediatype" type="xs:string" />
<xs:element minOccurs="0" name="mediacount" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="releasedate" type="xs:string" />
<xs:element minOccurs="0" name="coding" type="xs:string" />
<xs:element minOccurs="0" name="supergenre" type="xs:string" />
<xs:element minOccurs="0" name="features" type="xs:string" />
<xs:element minOccurs="0" name="genre" type="xs:string" />
<xs:element minOccurs="0" name="pricedate" type="xs:date" />
<xs:element minOccurs="0" name="price" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="rrp" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="distribprice" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="import" type="xs:string" />
<xs:element minOccurs="0" name="stocklevel" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="avaliable" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="AudioFormat" type="xs:string" />
<xs:element minOccurs="0" name="runtime" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="agelimit" type="xs:string" />
<xs:element minOccurs="0" name="tracks">
<xs:complexType mixed="true">
<xs:sequence minOccurs="0">
<xs:element minOccurs="0" maxOccurs="unbounded" name="track">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="name" type="xs:string" />
<xs:element minOccurs="0" name="cdno" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="trackno" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="artist" type="xs:string" />
<xs:element minOccurs="0" name="asfclip" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="notes">
<xs:complexType mixed="true">
<xs:sequence minOccurs="0">
<xs:element minOccurs="0" name="note">
<xs:complexType mixed="true">
<xs:sequence minOccurs="0">
<xs:element minOccurs="0" name="text" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="pricecode" type="xs:string" />
<xs:element minOccurs="0" name="director" type="xs:string" />
<xs:element minOccurs="0" name="language" type="xs:string" />
<xs:element minOccurs="0" name="VideoFormat" type="xs:string" />
<xs:element minOccurs="0" name="platform" type="xs:string" />
<xs:element minOccurs="0" name="conductor" type="xs:string" />
<xs:element minOccurs="0" name="deleted" type="xs:unsignedByte" />
</xs:choice>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Is there also a way of dynamically changing the Output column in the XML Source component of SSIS. If this is possible the is would be simpler to let SSIS do all the work by obtaining an XML file, loop thru the Output columns and import. The move on to the next one.
I am not a .net programmer so would appreciate if most of the suggestions could be SSIS or T-SQL.
Much appreciated.
Ismail
March 8, 2012 at 7:36 am
ismailmallum (3/8/2012)
Is there also a way of dynamically changing the Output column in the XML Source component of SSIS. If this is possible the is would be simpler to let SSIS do all the work by obtaining an XML file, loop thru the Output columns and import. The move on to the next one.
Only with third party tools. Check out the following thread in the SSIS forum: www.sqlservercentral.com/Forums/Topic1260005-148-1.aspx
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 8, 2012 at 7:50 am
Question would be: why are you looking to change the output column?
You have what looks to be a well-defined structure to bring in, which from your description seems to be importable just by looping through the files. How do you see some dynamic control for the output helping in this process?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 8, 2012 at 11:49 pm
Well this is the easiest xml to do. But i have about 20 XML files to import. And creating 20 different connections could be a propblem IF they add extra XML files. So i was thinking of looping thru the XSD files, get the columns into SQL with some data scrubbing then dynamically create the tables and bulin Insert each XML. The XPath query could then be dynamic. I have managed to get the dynamic table creation but the bulk import seems to be putting a spanner in the works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply