Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Bulk Load - XML Schema Help!

    Hello ,

    Im really stuck with a problem that’s been driving me nuts for a day or so now. I hope someone can help.

    Iv written a script to grab an XML file from the web , and the Bulk load it into my database. It works fine when I only use a very basic schema file, yet when I try to expand it to include additional attributes , im completely stuck.

    Heres some example XML

    Code:
    <xml>
    <incident>
    <assigned_to display_value="Some Assignee">1dc773d00a0a3c1b0078888f314c08ab</assigned_to>
    <sys_id>768b2f7d0a0a3c1b01de0c4a0c9f9e5d</sys_id>
    <approval>not requested</approval>
    <u_3rd_party_ref_no/>
    <state>8</state>
    <sys_updated_on>2009-06-15 06:53:10</sys_updated_on>
    <follow_up/>
    <caused_by/>
    <u_closure_code/>
    <active>true</active>
    <due_date/>
    <parent/>
    <closed_at/>
    <delivery_plan/>
    <close_notes/>
    <watch_list/>
    <business_stc/>
    <severity>3</severity>
    <sys_created_on>2009-05-25 06:56:52</sys_created_on>
    <caller_id display_value="Some Caller Name">1dc76ba40a0a3c1b010f1da0cca708d5</caller_id>
    <incident_state>9</incident_state>
    <business_duration/>
    <number>INC00137</number>
    <sys_mod_count>6</sys_mod_count>
    <u_business_service display_value="some business service">d5bdf2180a0a3c1b016460a73dae94b8</u_business_service>
    <short_description>DP Score Guide</short_description>
    <u_sub_classification>Standard Request</u_sub_classification>
    <reassignment_count>0</reassignment_count>
    <made_sla>true</made_sla>
    <activity_due/>
    <opened_by display_value="Some Name">459e1cf20a0a3c1b01660ab54b4e3282</opened_by>
    <sys_updated_by>system</sys_updated_by>
    <u_incident_manager display_value="Some Name">0faa87f20a0a3c1b00b09592e72af3b1</u_incident_manager>
    <assignment_group display_value="Application Support">50deaf490a0a3c1b005774a3f84c190c</assignment_group>
    <correlation_display/>
    <priority>4</priority>
    <group_list/>
    <sys_domain>global</sys_domain>
    <description>
    Outage information
    </description>
    <impact>2</impact>
    </incident>
    </xml>
    Here is my mapping XML schema file Im using to test the bulk load. This worked fine before I entered any <attribute> fields..


    Code:
    <?xml version="1.0" ?>
    <Schema xmlns="urn:schemas-microsoft-com:xml-data" 
            xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
            xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 
    
       <ElementType name="u_business_service" dt:type="string">
    	<AttributeType name="display_value" dt:type="string"/>
       </ElementType>
       <AttributeType name="display_value" dt:type="string"/>
       <ElementType name="description" dt:type="string" />
       <ElementType name="priority" dt:type="int" />
       <ElementType name="short_description" dt:type="string" />
    
    
       <ElementType name="xml" sql:is-constant="1">
          <element type="incident" />
       </ElementType>
    
       <ElementType name="incident"  sql:relation="Incidents">
          <element type="u_business_service"  sql:field="u_business_service" />
    	  <attribute type="display_value" sql:field="business_service"/>
          <element type="description" sql:field="description" />
          <element type="priority" sql:field="priority" />
    	  <element type="short_description" sql:field="short_description" />
       </ElementType>
    
    </Schema>


    As a reference , here is the same Schema that works fine .. with no attributes.

    Code:
    <?xml version="1.0" ?>
    <Schema xmlns="urn:schemas-microsoft-com:xml-data" 
            xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
            xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 
    
       <ElementType name="u_business_service" dt:type="string"/>
       <AttributeType name="display_value" dt:type="string"/>
       <ElementType name="description" dt:type="string" />
       <ElementType name="priority" dt:type="int" />
       <ElementType name="short_description" dt:type="string" />
    
    
       <ElementType name="xml" sql:is-constant="1">
          <element type="incident" />
       </ElementType>
    
       <ElementType name="incident"  sql:relation="Incidents">
          <element type="u_business_service"  sql:field="u_business_service" />
          <element type="description" sql:field="description" />
          <element type="priority" sql:field="priority" />
          <element type="short_description" sql:field="short_description" />
       </ElementType>
    </Schema>

    Im dead set stuck with this .. Can anyone please help ? I really need to include the “display_value’ attributes in the bulk load, I want them to go into their own column , just like the elements are.
    Last edited by Cheyne; 06-23-09 at 03:07.

  2. #2
    Join Date
    Jun 2009
    Posts
    66
    The relational model and true relational databases does not have XML datatypes.

    Do you mean you wish to load XML into a non-relational model in a non-relational database into a proprietary implementation without portability and poor performance? You can represent the XML schema in the DB as a relational structure instead of trying to store water in a built-from-paper pan.
    Last edited by sqlguru; 06-23-09 at 10:05.

  3. #3
    Join Date
    Mar 2009
    Posts
    349
    that is a little curt and troglodyte. many people parse xml files and store the data relationallly and that is perfectly legit because the one good thing XML is decent at is transfer.

  4. #4
    Join Date
    Jun 2009
    Posts
    3
    That’s ok , I understand what he’s trying to say. Maybe I should of mentioned a few other points.

    1. Im not a DB guy. I Work with SQL very often , but still only really know what I need to get by. (I should probably hang out in these forums more often)
    2. This data. Is going to be bulk loaded in batches, every few hours or so, but the record count will be fairly low, around the 3000 rows mark. Querying this is very fast , and performance isn’t a concern. Having all the data I need in a single table is my preference right now , so that’s what im trying to do. Relationship models and more complicated structures aren’t needed right now.

    My only bottle neck is those attributes. Can anyone help me fix that schema ?

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    Any takers ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •