Import XML into different sql tables

• PSB

  SSChampion

  Points: 13195

  More actions

  November 11, 2009 at 8:30 am

  #385762

  Hi,

  I have an XMl file that is similar to the given below :

  <?xml version="1.0" encoding="UTF-8" ?>

  <Ads>

  <Ad>

  <AdId>120</AdId>

  <Title></Title>

  <Description><p>SAN MARCOS ISD Crockett Elementary, Grades K-5 1300 Girard Avenue Rick LaBuhn, Principal (512)393-6400</p></Description>

  <Price>100</Price>

  <Location>Austin</Location>

  <DateCreated>7/15/2009 12:38:06 PM</DateCreated>

  <DateDisplay>7/19/2009</DateDisplay>

  <SubCategoryName>Schools</SubCategoryName>

  <MainCategoryName>Education</MainCategoryName>

  <PremiumFlag>True</PremiumFlag>

  <AdType>1</AdType>

  <EmailAddress>pbaruah@thegreensheet.com</EmailAddress>

  </Ad>

  <Ad>

  <AdId>1201</AdId>

  <Title></Title>

  <Description><p>MANOR ISD Bluebonnet Trail Elementary 11316 Farmhaven Austin, 78754 George Arenaz, Principal (512)278-4125 </p></Description>

  <Price>1001</Price>

  <Location>Dallas</Location>

  <DateCreated>7/20/2009 11:30:02 PM</DateCreated>

  <DateDisplay>7/20/2009</DateDisplay>

  <SubCategoryName>Townhomes and Condos for Rent</SubCategoryName>

  <MainCategoryName>REAL ESTATE</MainCategoryName>

  <PremiumFlag>True</PremiumFlag>

  <AdType>1</AdType>

  <EmailAddress>mbaruah@thegreensheet.com</EmailAddress>

  </Ad>

  <Photos>

  <Photo>

  <AdId>120</AdId>

  <BytesFull></BytesFull>

  <BytesSmall></BytesSmall>

  <BytesMedium></BytesMedium>

  <IsMainPreview>True</IsMainPreview>

  <DateCreated>11/10/2009 4:18:23 PM</DateCreated>

  <UploadedPath>C:\Documents and Settings\xyz\Desktop\Profile.jpg</UploadedPath>

  <ImageId></ImageId>

  </Photo>

  <Photo>

  <AdId>1201</AdId>

  <BytesFull></BytesFull>

  <BytesSmall></BytesSmall>

  <BytesMedium></BytesMedium>

  <IsMainPreview>True</IsMainPreview>

  <DateCreated>11/19/2009 5:19:25 PM</DateCreated>

  <UploadedPath>C:\Documents and Settings\xyz\Desktop\abcd.jpg</UploadedPath>

  <ImageId></ImageId>

  </Photo>

  </Photos>

  </Ads>

  I have to populate two tables 1) Ads table 2) Photos table based on the information from the above XML .

  I am using SSIS . If SSIS encounters <Photos> , then the Photos tables needs to be populated

  else if <Ad> , the Ads table .

  Any help appreciated .

  Thanks

• GSquared

  SSC Guru

  Points: 260824

  More actions

  November 11, 2009 at 9:19 am

  #1077879

  I generally use a staging table for that kind of thing. Import the whole thing into one table, then split it up in there with appropriate queries, in order to move it to the final tables.

  There are other ways to do it, but that's the one that's worked the best for me.

  - 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

• Erik Kutzler

  SSCarpal Tunnel

  Points: 4287

  More actions

  November 11, 2009 at 12:04 pm

  #1077972

  Have you tried adding an XML Source to a Data Flow?

  If you don't have the .xsd, the XML Source can create one for you. The XML Source will generate multiple outputs. Map the "Ad" output to your Ad table destination and the "Photo" output to your Photo table destination.

• PSB

  SSChampion

  Points: 13195

  More actions

  November 11, 2009 at 12:22 pm

  #1077982

  Thanks for your reply . I am using XML source as a data flow source and pointing it to photos as well as Ads table . This works fine .

  But The <Description> tag comes with many HTML tags for e.g

  <Description><p style="text-align: left;"><span style="font-size: 12pt;"><strong>2006 Mercedes C280 <br /> <br /> </strong></span> original MSRP $36,195. (281)000-0000. INDIVIDUAL</p></Description>

  How do I escape these dynamically ? I require the Description column in the Ads table to be as it is given in the XML i.e

  <p style="text-align: left;"><span style="font-size: 12pt;"><strong>2006 Mercedes C280 <br /> <br /> </strong></span> original MSRP $36,195. (281)000-0000. INDIVIDUAL</p>

  Thanks

• Erik Kutzler

  SSCarpal Tunnel

  Points: 4287

  More actions

  November 11, 2009 at 12:53 pm

  #1078003

  Right-click on your XML Source and select Show Advanced Editor.

  Go to the Input and Output Properties tab.

  Select the Description output and set the IsSorted property to True. Expand the Description output's Output Columns, select Ad_Id, and set the SortKeyPosition = 1.

  Do the same for the Ad output.

  Add a Merge Join to your data flow. Connect the Ad output of your XML source to it as the Merge Join Left Input. Connect the Description output of your XML source to it as the Merge Join Right Input.

  Edit the Merge Join and select all the columns from both XML sources that you want to see. Your Ad & Description are now merged.