Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 12th, 2003, 09:17 AM
Friend of Wrox
 
Join Date: Aug 2003
Posts: 205
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql:relationship

I am using the xml capability of sql server 2000. I am trying to insert data using an XSD either through an updategram or the XML Bulk Upload. However, the information goes to two tables. I am using a SQL relationship entity to link the table, but am getting an error message 'the parent/child table of the relationship on 'fdf_xml' does not match.' What am I doing wrong? Below is the 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 name="transactionsToFields" parent="imm_fdr_fields" parent-key="transaction_id"
                child="imm_fdr_transaction_information" child-key="transaction_id"/>
        </xsd:appinfo>
    </xsd:annotation>
    <xsd:element name="root">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="fdf_xml" sql:relation="imm_fdr_fields" sql:relationship="transactionsToFields">
                    <xsd:complexType>
                            <xsd:sequence>
                                <xsd:element name="transaction_information" sql:relation="imm_fdr_transaction_information" sql:relationship="transactionsToFields">
                                    <xsd:complexType>
                                        <xsd:sequence>
                                            <xsd:element name="transaction_id" type="xsd:string"/>
                                            <xsd:element name="template_name" type="xsd:string"/>
                                            <xsd:element name="country" type="xsd:string"/>
                                            <xsd:element name="submitted_by" type="xsd:string"/>
                                            <xsd:element name="officer_code" type="xsd:integer"/>
                                            <xsd:element name="sent_date" type="xsd:string"/>
                                        </xsd:sequence>
                                    </xsd:complexType>
                                </xsd:element>
                                <xsd:element name="txtTransactionID" sql:field="transaction_id" type="xsd:string"/>
                                <xsd:element name="txtAccount" sql:field="field1" type="xsd:integer"/>
                                <xsd:element name="txtAmount" sql:field="field2" type="xsd:decimal"/>
                                <xsd:element name="txtCustomer" sql:field="field3" type="xsd:string"/>
                                <xsd:element name="txtNote" sql:field="field4" type="xsd:string"/>
                            </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>
 
Old August 12th, 2003, 12:23 PM
Friend of Wrox
 
Join Date: Aug 2003
Posts: 205
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Never mind. I figured it out.
 
Old December 22nd, 2004, 06:02 AM
Registered User
 
Join Date: Dec 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear Yehuda,

I'm having problem in mapping my .xsd file.
I'll appreciate if you can help me since as I saw you dealt with the same problems.

I'll describe my problem:
I'm using SQLXMLBulkLoad to load the xml data

my .xsd schema is:

<xsd:element name="OrderDetail"/>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CompanyID"/>
<xsd:element name="CompanyName"/>
<xsd:element name="OrderDate"/>
<xsd:element name="Price"/>
</xsd:sequence>
</xsd:complexType>

example xml:

<OrderDetail>
<CompanyID>123</CompanyID>
<CompanyName>ABC</CompanyName>
<OrderDate>2002-10-10</OrderDate>
<Price>100$</Price>
</OrderDetail>

I've 2 tables which I want to update using this xml file:

Table Company:
===========
CompanyID (primary)
CompanyName

Table Orders:
==========
CompanyID (primary, foreign from Company)
OrderDate
Price

I have not succee to put the right mapping.
how do I put the "sql:relation" annotation in 'OrderDetail' when its data should update the two tables in fields that are not the primary-key ? (CompanyName --> Company
and
OrderDate,Price --> Orders)
Best Regards







Similar Threads
Thread Thread Starter Forum Replies Last Post
One To One RelationShip prasanta2expert SQL Language 0 November 7th, 2006 06:30 AM
O/R mapping many to many relationship popoxinhxan BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 August 17th, 2006 11:18 PM
one-to-many relationship sample matfis Classic ASP Databases 2 March 18th, 2005 11:37 AM
Many To Many Relationship samersult BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 1 January 26th, 2005 07:09 AM
report without relationship mateenmohd Access 2 December 29th, 2004 06:55 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.