Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSql XML Column modification error with XSD using replace value of


TSql XML Column modification error with XSD using replace value of

Author
Message
denis.gilbert
denis.gilbert
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 40
Good day,

The following test is working:

DECLARE @XmlTest TABLE
( ID int,
MyContent Xml
)

declare @aaa XML

set @aaa = '<Root><MyNode Qualifier="0" Code="AAA" Deleted="true">9.99</MyNode><MyNode Qualifier="0" Code="AAA" Deleted="false">10.99</MyNode></Root>'
insert into @XmlTest (ID, MyContent)
values(1,@aaa)
set @aaa = '<Root><MyNode Qualifier="0" Code="BBB" Deleted="false">9.99</MyNode></Root>'
insert into @XmlTest (ID, MyContent)
values(2,@aaa)

select * from @XmlTest

-- The node selection [@Code="AAA" and @Deleted="false"] is unique, so I want to be able to "delete it" logically
UPDATE @XmlTest
SET MyContent.modify('replace value of (/Root/MyNode[@Code="AAA" and @Deleted="false"]/@Deleted)[1] with true()')
WHERE ID = 1

select * from @XmlTest


But with a XSD I cannot make it work as follow:

DECLARE @XmlTest TABLE
( ID int,
MyContent Xml(CONTENT TestSchema)
)


I have error message like

Msg 2234, Niveau 16, État 1, Ligne 19
XQuery [@XmlTest.MyContent.modify()]: Operator "=" cannot be applied to "<anonymous>" and "xs:string"


Here is the schema (sorry I don't know how to do it outside a database for easy testing) :

DROP XML SCHEMA COLLECTION TestSchema

create xml schema collection TestSchema as
'<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:simpleType name="AmountType">
<xs:restriction base="xs:decimal">
<xs:minInclusive value="0"/>
<xs:maxInclusive value="9999999999"/>
<xs:fractionDigits value="2"/>
</xs:restriction>
</xs:simpleType>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="MyNode" maxOccurs="unbounded" minOccurs="1" sql:is-constant="1">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="AmountType">
<xs:attribute name="Qualifier" use="required">
<xs:simpleType>
<xs:restriction base="xs:integer">
<xs:minInclusive value="0"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="Code" use="required">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="15"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="Deleted" use="required">
<xs:simpleType>
<xs:restriction base="xs:boolean">
<xs:pattern value="true"/>
<xs:pattern value="false"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'

I tried this modification but it failed too:

<xs:attribute type="xs:boolean" name="Deleted" use="required" />

Thanks in advance
denis.gilbert
denis.gilbert
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 40
Thanks to Mikael Erikssen that replied to me on stack overflow forum.

A solution to this issue is to remove the restriction on my Boolean attribute and probably more important only use true() or false() function to test or set the attribute as follow:

-- XSD :
<xs:attribute type="xs:boolean" name="Deleted" use="required" />

-- Update

UPDATE @XmlTest
SET MyContent.modify('replace value of (/Root/MyNode[@Code="AAA" and @Deleted=false()]/@Deleted)[1] with true()')
WHERE ID = 1
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search