XML Bulk Load into Single Table

Hi all,

I've got an XML file which is from the vendor which I can't change at all. Its in the following format.

<Archive>
  <Events>
    <Event OccurrenceTime="2009-06-15T23:57:50"
    ArrivalTime="2009-06-28T09:39:34.500" Type="20101"
    DivisionID="186352" Priority="3" Class="39" Alarm="1"
    Occurrences="1"
    Message="ALLISON, Jacqueline was denied access through BEN 119 L1 - VH U35 B06. Reason: The access on the card is out of date."
    Details="&#238;&#8364;&#8364;&#238;&#8364;&#8364;">
      <Card Type="1" Number="15188" FacilityCode="101550"
      IssueLevel="1" />
      <Relation FTItemID="187320" Type="2" />
      <Relation FTItemID="220406" Type="0" />
      <Relation FTItemID="187020" Type="18" />
    </Event>
    <Event OccurrenceTime="2009-06-15T23:57:52"
    ArrivalTime="2009-06-28T09:39:34.530" Type="20101"
    DivisionID="186352" Priority="3" Class="39" Alarm="0"
    Occurrences="1"
    Message="ALLISON, Jacqueline was denied access through BEN 119 L1 - VH U35 B06. Reason: The access on the card is out of date."
    Details="&#238;&#8364;&#8364;&#238;&#8364;&#8364;">
      <Card Type="1" Number="15188" FacilityCode="101550"
      IssueLevel="1" />
      <Relation FTItemID="187320" Type="2" />
      <Relation FTItemID="220406" Type="0" />
      <Relation FTItemID="187020" Type="18" />
    </Event>

  </Events>
</Archive>

I require to only insert the FTItemID if the type = "0", now this would be dead simple to do if the XML file had some UID to use so I can't insert this into 2 tables which all examples on the internet are showing.

This is the XML schema file i've tried to get working so far;

<ElementType name="Events" sql:is-constant="1">
    <element type="Event" />
    <element type="Relation" />
</ElementType>

<ElementType name="Event"  sql:relation="Event" >
    <AttributeType name="OccurrenceTime" dt:type="string" />
    <attribute type="OccurrenceTime" sql:field="OccurrenceTime" />

    <AttributeType name="Message" dt:type="string" />
    <attribute type="Message" sql:field="Message" />
</ElementType>


<ElementType name="Relation" sql:relation="Event">
    <AttributeType name="FTItemID" dt:type="string" />
    <attribute type="FTItemID" sql:limit-field="FTItemID"  sql:limit-value="0"/>
</ElementType>

If I remove sql:limit-field="FTItemID" sql:limit-value="0" it inserts fine but doesn't insert FTItemID.

Any help would receive major thumbs up from me :)

ansaurus

tags:

views:

answers:

XML Bulk Load into Single Table

related questions