"sgcummi***@sbcglobal.net" wrote:

> Hello everyone.  I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
> have a question regarding how to define an xml-schema to bulk load an
> XML document that I get from our system.
>
> Here's what the XML document looks like:
>
> <CatalogDelta>
>     <RevisionID>1.0</RevisionID>
>     <CatalogVersion>195</CatalogVersion>
>     <deletes>
>          <album id="123" />
>          <song id="2345" />
>          <song id="4563" />
>     </deletes>
> </CatalogDelta>
>
> What I want to do is get the xml bulk loaded into three SQL database
> tables:
>
> Table CatalogDelta
> (  RevisionID varchar(50),
>    CatalogVersion varchar(50)
> )
>      1.0   |   195
>
> Table album_deletes
> ( id varchar(50) )
>      123
>
> table song_deletes
> ( id varchar(50) )
>     2345
>     4563
>
> Here's the XML-Schema I've been trying to use but can't seem to get
> past the <deletes> element.
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
>             xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
>   <xsd:element name="RevisionID" sql:field="revision_id"
> sql:datatype="nvarchar(50)" />
>   <xsd:element name="CatalogVersion" sql:field="catalog_version"
> sql:datatype="nvarchar(50)" />
>
>   <!-- CatalogDelta -->
>   <xsd:group name="DeltaCatalogGroup">
>     <xsd:sequence>
>       <xsd:element ref="RevisionID"/>
>       <xsd:element ref="CatalogVersion" />
>     </xsd:sequence>
>   </xsd:group>
>
>   <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
>     <xsd:complexType>
>       <xsd:group ref="DeltaCatalogGroup"/>
>     </xsd:complexType>
>   </xsd:element>
>
>   <!-- Deletes -->
>   <xsd:element name="deletes" sql:is-constant="1">
>     <xsd:complexType>
>       <xsd:all>
>         <!-- Delete Albums -->
>         <xsd:element name="album" sql:relation="album_deletes">
>           <xsd:complexType>
>             <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
>           </xsd:complexType>
>         </xsd:element>
>
>         <!-- Delete Songs -->
>         <xsd:element name="song" sql:relation="song_deletes">
>           <xsd:complexType>
>             <xsd:attribute name="id" type="xsd:string"
> sql:field="song_id" sql:datatype="nvarchar(50)" />
>           </xsd:complexType>
>         </xsd:element>
>      </xsd:all>
>    </xsd:complexType>
>   </xsd:element>
> </schema>
>
> When I bulk load using this schema, the contents get put into the
> CatalogDelta table but nothing gets put into the album_deletes or
> song_deletes tables.
>
> Can someone point out what I am doing wrong, please?
>
> Thank you for your help.
>
> Sincerely
> Steve Cummings
>
>

> Hello,
>
> Whenever you have children you have todefinerelationship in theschema:
> <xsd:annotation>
>                 <xsd:appinfo>
>                         <sql:relationship name="catalog_album"
>                         parent="CatalogDelta"
>                         child="album_deletes"
>                         parent-key="?"
>                         child-key="?"/>
>                 </xsd:appinfo>
>         </xsd:annotation>
>
> Then add the deletes element to the Catalog:
>
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
>         <xsd:sequence>
>                 <xsd:group ref="DeltaCatalogGroup"/>
>                 <xsd:element ref="deletes" />
>         </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <xsd:element name="album" sql:relation="album_deletes"
> sql:relationship="catalog_album">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
>
> You need to figure out how the tables related to each other and describe
> this in theschema.
>
> Take a look at
> this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
>
> I hope this helps.
>
> Regards,
> Monica Frintu
>
>
>
> "sgcummi***@sbcglobal.net" wrote:
> > Hello everyone.  I'm fairly new toSQLXMLBulkLoadand XML-Schemas and
> > have a question regarding how todefinean xml-schemato bulk load an
> > XML document that I get from our system.
>
> > Here's what the XML document looks like:
>
> > <CatalogDelta>
> >     <RevisionID>1.0</RevisionID>
> >     <CatalogVersion>195</CatalogVersion>
> >     <deletes>
> >          <album id="123" />
> >          <song id="2345" />
> >          <song id="4563" />
> >     </deletes>
> > </CatalogDelta>
>
> > What I want to do is get the xml bulk loaded into three SQL database
> > tables:
>
> > Table CatalogDelta
> > (  RevisionID varchar(50),
> >    CatalogVersion varchar(50)
> > )
> >      1.0   |   195
>
> > Table album_deletes
> > ( id varchar(50) )
> >      123
>
> > table song_deletes
> > ( id varchar(50) )
> >     2345
> >     4563
>
> > Here's the XML-SchemaI've been trying to use but can't seem to get
> > past the <deletes> element.
>
> > <xsd:schemaxmlns:xsd="http://www.w3.org/2001/XMLSchema"
> >             xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> >   <xsd:element name="RevisionID" sql:field="revision_id"
> > sql:datatype="nvarchar(50)" />
> >   <xsd:element name="CatalogVersion" sql:field="catalog_version"
> > sql:datatype="nvarchar(50)" />
>
> >   <!-- CatalogDelta -->
> >   <xsd:group name="DeltaCatalogGroup">
> >     <xsd:sequence>
> >       <xsd:element ref="RevisionID"/>
> >       <xsd:element ref="CatalogVersion" />
> >     </xsd:sequence>
> >   </xsd:group>
>
> >   <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> >     <xsd:complexType>
> >       <xsd:group ref="DeltaCatalogGroup"/>
> >     </xsd:complexType>
> >   </xsd:element>
>
> >   <!-- Deletes -->
> >   <xsd:element name="deletes" sql:is-constant="1">
> >     <xsd:complexType>
> >       <xsd:all>
> >         <!-- Delete Albums -->
> >         <xsd:element name="album" sql:relation="album_deletes">
> >           <xsd:complexType>
> >             <xsd:attribute name="id" sql:field="album_id"
> > type="xsd:string" sql:datatype="nvarchar(05)" />
> >           </xsd:complexType>
> >         </xsd:element>
>
> >         <!-- Delete Songs -->
> >         <xsd:element name="song" sql:relation="song_deletes">
> >           <xsd:complexType>
> >             <xsd:attribute name="id" type="xsd:string"
> > sql:field="song_id" sql:datatype="nvarchar(50)" />
> >           </xsd:complexType>
> >         </xsd:element>
> >      </xsd:all>
> >    </xsd:complexType>
> >   </xsd:element>
> > </schema>
>
> > When I bulk load using thisschema, the contents get put into the
> > CatalogDelta table but nothing gets put into the album_deletes or
> > song_deletes tables.
>
> > Can someone point out what I am doing wrong, please?
>
> > Thank you for your help.
>
> > Sincerely
> > Steve Cummings- Hide quoted text -
>
> - Show quoted text -