Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSql XML Column modification error with XSD using replace value of Expand / Collapse
Author
Message
Posted Friday, March 08, 2013 9:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:30 PM
Points: 2, Visits: 24
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
Post #1428845
Posted Saturday, March 09, 2013 4:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:30 PM
Points: 2, Visits: 24
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
Post #1428942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse