Post reply

SQLXML Bulk Load .... multi element XML File to One Table

  • June 12, 2006 at 11:08 am

    #94047

    Sorry if this is the wrong place but could not see any other places....

    I am probably missing something... but I am getting annoyed with all the examples for the schemas and loading the XML into the database as it assumes that there is a clear 1-1 mapping between element and table and that it would not be possible if a complete record for a table entry might be contained nested within several elements.... sorry rant over....

    I have the following xml.... and so far the following xsd..... but I need to specify some relation in the XSD that will help map the XML to the correct fields the XML maps to ONE table.

    XML.....

    <?xml version="1.0"?>

    <root>

      <artefact doc_ref="Trent1000ARTiSAN" doc_type="12" doc_name="Trent1000ARTiSAN">

        <instance type_id="17" doc="Trent1000ARTiSAN" sec="05 Component View::Control CPU::Application Layer::Engine Events::IdleDetection.aboveIdleFI"/>

    ...

    ...

    ...

    ...

    ...

    </artefact>

    </root>

    XSD....

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

     xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

    <xsd:annotation>

      <xsd:appinfo>

        <sql:relationship ?????????????????????????????? />

      </xsd:appinfo>

    </xsd:annotation>

     <xsd:element name="artefact" sql:relation="TBL_INSTANCES">

       <xsd:complexType>

         <xsd:sequence>

           <xsd:element name="instance" sql:relation="TBL_INSTANCES" sql:relationship="?????????????????????">

      <xsd:complexType>

        <xsd:attribute name="type_id" type="xsd:int" use="required" sql:field="TYPE_ID" sql:datatype="bigint"/>

        <xsd:attribute name="doc" type="xsd:string" use="required" sql:field="DOC" sql:datatype="varchar(200)"/>

        <xsd:attribute name="sec" type="xsd:string" use="required" sql:field="SEC" sql:datatype="varchar(200)"/>

      </xsd:complexType>

           </xsd:element>

         </xsd:sequence>

         <xsd:attribute name="doc_ref" type="xsd:string" use="required" sql:field="DOC_ID" sql:datatype="varchar(200)"/>      

         <xsd:attribute name="doc_name" type="xsd:string" use="required" sql:field="DOC_NAME" sql:datatype="varchar(200)"/>

         <xsd:attribute name="doc_type" type="xsd:string" use="required" sql:field="DOC_TYPE" sql:datatype="bigint"/>

       </xsd:complexType>

     </xsd:element>

    </xsd:schema>

     

  • June 15, 2006 at 4:17 am

    #643898

    i believe the issue is that one complex type must map to one table.... can anyone confirm this is true?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply