Trying to update in a field that contains xml

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to update in a field that contains xml

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-11-11 : 06:45:14

Hi

Trying to run this SQL script

update Promotions

set PromotionDiscountData = '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-11T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-12T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule"><NumberOfUsesAllowed>1</NumberOfUsesAllowed></PromotionRuleBase><PromotionRuleBase xsi:type="ProductIdPromotionRule"><ProductIds><int>55232</int></ProductIds><RequireQuantity>false</RequireQuantity><Quantity>1</Quantity><AndTogether>false</AndTogether></PromotionRuleBase></ArrayOfPromotionRuleBase>'

where PromotionDiscountData = '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-18T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-12T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule"><NumberOfUsesAllowed>1</NumberOfUsesAllowed></PromotionRuleBase><PromotionRuleBase xsi:type="ProductIdPromotionRule"><ProductIds><int>55232</int></ProductIds><RequireQuantity>false</RequireQuantity><Quantity>1</Quantity><AndTogether>false</AndTogether></PromotionRuleBase></ArrayOfPromotionRuleBase>'

but getting this error

Msg 402, Level 16, State 1, Line 1
The data types xml and varchar are incompatible in the equal to operator.

any idea how to fix this?

Basically within each cell I am trying to change the StartDate only

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 07:07:34

here's an illustration of your issue


declare @t table
(
x xml 
)
insert @t
values('<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
- <PromotionRuleBase xsi:type="StartDatePromotionRule">
  <StartDate>2013-11-18T00:00:00</StartDate> 
  </PromotionRuleBase>
- <PromotionRuleBase xsi:type="ExpirationDatePromotionRule">
  <ExpirationDate>2014-01-12T00:00:00</ExpirationDate> 
  </PromotionRuleBase>
- <PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule">
  <NumberOfUsesAllowed>1</NumberOfUsesAllowed> 
  </PromotionRuleBase>
- <PromotionRuleBase xsi:type="ProductIdPromotionRule">
- <ProductIds>
  <int>55232</int> 
  </ProductIds>
  <RequireQuantity>false</RequireQuantity> 
  <Quantity>1</Quantity> 
  <AndTogether>false</AndTogether> 
  </PromotionRuleBase>
  </ArrayOfPromotionRuleBase>')

  update @t
  set x.modify('replace value of (/ArrayOfPromotionRuleBase/PromotionRuleBase/StartDate/text())[1] with "2013-11-11T00:00:00" cast as xs:dateTime?')
  select * from @t


output
--------------------------------
<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
- <PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-11T00:00:00</StartDate></PromotionRuleBase>
- <PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-12T00:00:00</ExpirationDate></PromotionRuleBase>
- <PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule"><NumberOfUsesAllowed>1</NumberOfUsesAllowed></PromotionRuleBase>
- <PromotionRuleBase xsi:type="ProductIdPromotionRule">
- <ProductIds><int>55232</int></ProductIds><RequireQuantity>false</RequireQuantity><Quantity>1</Quantity><AndTogether>false</AndTogether></PromotionRuleBase></ArrayOfPromotionRuleBase>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-11-11 : 07:21:12

Ok so your saying its the actual date format that is causing the problem? Is there anyway at all to update this via SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 07:24:25

quote:
Originally posted by deanglen

Ok so your saying its the actual date format that is causing the problem? Is there anyway at all to update this via SQL?

Nope
I never commented anything on date format

I just gave you the t-sql query to do the update using XML Xpath functions
you just need to replace @t with your actual tablename and also x with actual XML column name and you should be all set so far as the XML structure is exactly how you posted.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-11-11 : 08:01:25

Ok so trying this

declare Promotions
(
PromotionRuleData xml
)
insert Promotions
values('<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
- <PromotionRuleBase xsi:type="StartDatePromotionRule">
<StartDate>2013-11-18T00:00:00</StartDate>
</PromotionRuleBase>
- <PromotionRuleBase xsi:type="ExpirationDatePromotionRule">
<ExpirationDate>2014-01-12T00:00:00</ExpirationDate>
</PromotionRuleBase>
- <PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule">
<NumberOfUsesAllowed>1</NumberOfUsesAllowed>
</PromotionRuleBase>
- <PromotionRuleBase xsi:type="ProductIdPromotionRule">
- <ProductIds>
<int>55232</int>
</ProductIds>
<RequireQuantity>false</RequireQuantity>
<Quantity>1</Quantity>
<AndTogether>false</AndTogether>
</PromotionRuleBase>
</ArrayOfPromotionRuleBase>')

update Promotions
set PromotionRuleData.modify('replace value of (/ArrayOfPromotionRuleBase/PromotionRuleBase/StartDate/text())[1] with "2013-11-11T00:00:00" cast as xs:dateTime?')
select * from Promotions

and getting

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 08:10:19

quote:
Originally posted by deanglen

Ok so trying this

declare @Promotions table
(
PromotionRuleData xml
)
insert @Promotions
values('<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
- <PromotionRuleBase xsi:type="StartDatePromotionRule">
<StartDate>2013-11-18T00:00:00</StartDate>
</PromotionRuleBase>
- <PromotionRuleBase xsi:type="ExpirationDatePromotionRule">
<ExpirationDate>2014-01-12T00:00:00</ExpirationDate>
</PromotionRuleBase>
- <PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule">
<NumberOfUsesAllowed>1</NumberOfUsesAllowed>
</PromotionRuleBase>
- <PromotionRuleBase xsi:type="ProductIdPromotionRule">
- <ProductIds>
<int>55232</int>
</ProductIds>
<RequireQuantity>false</RequireQuantity>
<Quantity>1</Quantity>
<AndTogether>false</AndTogether>
</PromotionRuleBase>
</ArrayOfPromotionRuleBase>')

update @Promotions
set PromotionRuleData.modify('replace value of (/ArrayOfPromotionRuleBase/PromotionRuleBase/StartDate/text())[1] with "2013-11-11T00:00:00" cast as xs:dateTime?')
select * from @Promotions

and getting

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

You missed keyword table
also table variable names should start with @

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources