TSql XML Column modification error with XSD using replace value of

• denis.gilbert

  Grasshopper

  Points: 22

  More actions

  March 8, 2013 at 9:46 pm

  #275025

  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

  Grasshopper

  Points: 22

  More actions

  March 9, 2013 at 4:07 pm

  #1595271

  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