I am having a problem with an xsd design (again). The client has decided to make a change and everything I try does not work. They have ADDED a nested tag to a nested tag - a person can have multiple addresses. There is a <PERSONID> tag that appears under
the <LIST> tag at the same level as the new tags. I need the ADDRESS info to end up in an ADDRESS table, with the PERSONID populated. The tags appear as:
<LIST><PERSONID>12345</PERSONID><ADDITIONAL ADDRESS><ADDRESS><ADDRESS_TYPE>Home</ADDRESS_TYPE><STREET>123 Main </STREET><CITY>Montreal</CITY></ADDRESS><ADDRESS><ADDRESS_TYPE>Business</ADDRESS_TYPE><STREET>695 Fuller Ave</STREET><CITY>Montreal</CITY></ADDR
ESS><ADDITIONAL ADDRESS></LIST>
Tables:
CREATE TABLE [dbo].[LIST] (
[personid] [int] NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ADDRESS] (
[personid] [int] NULL ,
[address_type] [char] (6) NULL,
[street] [char] (60) NULL ,
[city] [char] (50) NULL ,
) ON [PRIMARY]
GO
I tried the following schema, did not work. Any help would be greatly appreciated. Thanks.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation><xsd:appinfo><sql:relation ship name="LIST_ADDITIONAL_ADDRESS" parent="LIST" parent-key="personid" child="ADDITIONAL_ADDRESS" child-key="personid"/><sql:relationship name="LIST_ADDITIONA
L_ADDRESS_ADDRESS" parent="LIST_ADDITIONAL_ADDRESS" parent-key="personid" child="ADDITIONAL_ADDRESS_ADDRESS" child-key="personid"/></xsd:appinfo></xsd:annotation><xsd:element name="LIST" sql:relation="LIST"><xsd:complexType><xsd:sequence ><xsd:element name
="personid" type="xsd:string" minOccurs="0" sql:field="personid"/><xsd:element name="LIST_ADDITIONAL_ADDRESS" minOccurs="0" maxOccurs="unbounded" sql:relation="ADDITIONAL_ADDRESS" sql:relationship="LIST_ADDITIONAL_ADDRESS"><xsd:co mplextType><xsd:sequence>
<xsd:element name="ADDRESS" minOccurs="0" maxOccurs="unbounded" sql:relation="ADDRESS" sql:relationship="LIST_ADDITIONAL_ADDRESS_ADDRESS" ><xsd:complexType><xsd:sequence><xsd:element name="address_type" type="xsd:string" minOccurs="0" sql:field="address_ty
pe"/><xsd:element name="street" type="xsd:string" minOccurs="0" sql:field="street"/><xsd:element name="city" type="xsd:string" minOccurs="0" sql:field="city"/></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence><xsd:complextType><xsd:element></x
sd:sequence></xsd:complexType></element></xsd:schema>
Couple of things:
- I think you have an error at the end of your schema. You want to close
the complexType and element tags, not open new ones.
- I assume the "LIST_ADDITIONAL_ADDRESSES you refer to as the child in your
second relationship is the name of the first relatiionship? That doesn't
work. This can only refer to a table name.
- If all they did is add a wrapper element, can you use sql:is-constant?
--
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"darren" <anonymous@.discussions.microsoft.com> wrote in message
news:C5C0A64D-7EDD-4E9C-AECC-4A609EEAEA7B@.microsoft.com...
>I am having a problem with an xsd design (again). The client has decided to
>make a change and everything I try does not work. They have ADDED a nested
>tag to a nested tag - a person can have multiple addresses. There is a
><PERSONID> tag that appears under the <LIST> tag at the same level as the
>new tags. I need the ADDRESS info to end up in an ADDRESS table, with the
>PERSONID populated. The tags appear as:
> <LIST><PERSONID>12345</PERSONID><ADDITIONAL
> ADDRESS><ADDRESS><ADDRESS_TYPE>Home</ADDRESS_TYPE><STREET>123 Main
> </STREET><CITY>Montreal</CITY></ADDRESS><ADDRESS><ADDRESS_TYPE>Business</ADDRESS_TYPE><STREET>695
> Fuller Ave</STREET><CITY>Montreal</CITY></ADDRESS><ADDITIONAL
> ADDRESS></LIST>
> Tables:
> CREATE TABLE [dbo].[LIST] (
> [personid] [int] NULL ,
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ADDRESS] (
> [personid] [int] NULL ,
> [address_type] [char] (6) NULL,
> [street] [char] (60) NULL ,
> [city] [char] (50) NULL ,
> ) ON [PRIMARY]
> GO
> I tried the following schema, did not work. Any help would be greatly
> appreciated. Thanks.
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation><xsd:appinfo><sql:relation ship
> name="LIST_ADDITIONAL_ADDRESS" parent="LIST" parent-key="personid"
> child="ADDITIONAL_ADDRESS" child-key="personid"/><sql:relationship
> name="LIST_ADDITIONAL_ADDRESS_ADDRESS" parent="LIST_ADDITIONAL_ADDRESS"
> parent-key="personid" child="ADDITIONAL_ADDRESS_ADDRESS"
> child-key="personid"/></xsd:appinfo></xsd:annotation><xsd:element
> name="LIST"
> sql:relation="LIST"><xsd:complexType><xsd:sequence ><xsd:element
> name="personid" type="xsd:string" minOccurs="0"
> sql:field="personid"/><xsd:element name="LIST_ADDITIONAL_ADDRESS"
> minOccurs="0" maxOccurs="unbounded" sql:relation="ADDITIONAL_ADDRESS"
> sql:relationship="LIST_ADDITIONAL_ADDRESS"><xsd:co mplextType><xsd:sequence><xsd:element
> name="ADDRESS" minOccurs="0" maxOccurs="unbounded" sql:relation="ADDRESS"
> sql:relationship="LIST_ADDITIONAL_ADDRESS_ADDRESS" ><xsd:complexType><xsd:sequence><xsd:element
> name="address_type" type="xsd:string" minOccurs="0"
> sql:field="address_type"/><xsd:element name="street" type="xsd:string"
> minOccurs="0" sql:field="street"/><xsd:element name="city"
> type="xsd:string" minOccurs="0"
> sql:field="city"/></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence><xsd:complextType><xsd:element></xsd:sequence></xsd:complexType></element></xsd:schema>
|||that worked, thanks a lot for your help.
|||Glad to help.
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
This posting is provided "AS IS" with no warranties, and confers no rights.
"darren" <anonymous@.discussions.microsoft.com> wrote in message
news:62B6433B-C3CE-486F-B62A-C296D76BE433@.microsoft.com...
> that worked, thanks a lot for your help.
Showing posts with label xsd. Show all posts
Showing posts with label xsd. Show all posts
Wednesday, March 7, 2012
Problem with XMLNS version(while deploying report)
Our Report has xmlns=http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition.xsd
but the server for deploying our report has different namespace.....http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition.xsd.
Is there any way we can deploy reports with new namespace to the server?
Please guide us what we need to do to fix this problem. ?
ALL Reporting server experts....Kindly...Please look into this issue
You can deploy those reports to the older server version. You will need to remove any of the new 2005 features in the RDL to make it conform to the older definition and change the name space.
Or, you can upgrade to RS 2005.
Monday, February 20, 2012
problem with updateing foreign key to null value
Hi,
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob HuibersHi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:
> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob HuibersHi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:
> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>
problem with updateing foreign key to null value
Hi,
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob Huibers
Hi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers
|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:
> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob Huibers
Hi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers
|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:
> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>
Subscribe to:
Posts (Atom)