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 CummingsHello,
Whenever you have children you have to define relationship in the schema:
<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 the schema.
Take a look at
this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
I hope this helps.
Regards,
Monica Frintu
"sgcummings@.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
>|||On Apr 10, 2:54 pm, Monica Frintu [MSFT]
<MonicaFrintuM...@.discussions.microsoft.com> wrote:
> 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:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hello Monica!
Thank you for the information. It was very helpful. I was able to
extrapolate from what you provided and have everything working now.
It only took about 20 minutes to complete all the adjustments to get
the schema bulk load to work.
With much appreciation for your help.
Steve Cummings
vrkanaka wrote:
