xml-dml insert element with an attribute containig sql:variable

15 Nov 2005 1:53 AM

Steve

Hi,

I am trying to insert a new element into a typed xml column.

my problem is, how do you insert an element that has an attribute containig
a sql:variable i.e. TimeStamp="sql:variable("@Date")".
This returns an error.

update Statistics
set
EntriesByDate.modify('declare namespace
s="http://schemas/StatisticsEntriesByDate"; insert <s:Entries
TimeStamp="sql:variable("@Date")" Total="1" /> as last into
(/s:EntriesByDate)[1]')
where SiteId = @SiteId

My solution was to insert the element 'as last' and then update the last
element in the list, but that doesn't seem like the best way to do this.

Thanks

Stephen

15 Nov 2005 4:00 AM

Han

Something like,

declare @x xml
set @x='<a><b/></a>'
declare @v int
set @v=1

set @x.modify('
insert element c {attribute a1 {sql:variable("@v")}} as last
into (/a)[1]
')

select @x

Resulting,

<a>
<b />
<c a1="1" />
</a>

--
Pohwan Han. Seoul. Have a nice day.
<St***@somewhere.com> wrote in message

Show quoteHide quote

news:ek5ZcdY6FHA.2576@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I am trying to insert a new element into a typed xml column.
>
> my problem is, how do you insert an element that has an attribute
> containig
> a sql:variable i.e. TimeStamp="sql:variable("@Date")".
> This returns an error.
>
> update Statistics
> set
> EntriesByDate.modify('declare namespace
> s="http://schemas/StatisticsEntriesByDate"; insert <s:Entries
> TimeStamp="sql:variable("@Date")" Total="1" /> as last into
> (/s:EntriesByDate)[1]')
> where SiteId = @SiteId
>
> My solution was to insert the element 'as last' and then update the last
> element in the list, but that doesn't seem like the best way to do this.
>
>
> Thanks
>
> Stephen

18 Nov 2005 8:59 PM

Eugene Kogan [MSFT]

That or use your original syntax adding {} around sql:variable() in
attribute constructor:
...TimeStamp="{sql:variable("@Date")}"...
This is the way in XQuery to switch context between XML construction and
expressions.

Thus, you'll have:

update Statistics
set
EntriesByDate.modify('declare namespace
s="http://schemas/StatisticsEntriesByDate"; insert <s:Entries
TimeStamp="{sql:variable("@Date")}" Total="1" /> as last into
(/s:EntriesByDate)[1]')
where SiteId = @SiteId

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

Show quoteHide quote

"Han" <hp4***@kornet.net.korea> wrote in message
news:%23YRYdkZ6FHA.3648@tk2msftngp13.phx.gbl...
> Something like,
>
> declare @x xml
> set @x='<a><b/></a>'
> declare @v int
> set @v=1
>
> set @x.modify('
> insert element c {attribute a1 {sql:variable("@v")}} as last
> into (/a)[1]
> ')
>
> select @x
>
> Resulting,
>
> <a>
> <b />
> <c a1="1" />
> </a>
>
> --
> Pohwan Han. Seoul. Have a nice day.
> <St***@somewhere.com> wrote in message
> news:ek5ZcdY6FHA.2576@TK2MSFTNGP09.phx.gbl...
>> Hi,
>>
>> I am trying to insert a new element into a typed xml column.
>>
>> my problem is, how do you insert an element that has an attribute
>> containig
>> a sql:variable i.e. TimeStamp="sql:variable("@Date")".
>> This returns an error.
>>
>> update Statistics
>> set
>> EntriesByDate.modify('declare namespace
>> s="http://schemas/StatisticsEntriesByDate"; insert <s:Entries
>> TimeStamp="sql:variable("@Date")" Total="1" /> as last into
>> (/s:EntriesByDate)[1]')
>> where SiteId = @SiteId
>>
>> My solution was to insert the element 'as last' and then update the last
>> element in the list, but that doesn't seem like the best way to do this.
>>
>>
>> Thanks
>>
>> Stephen
>

17 Nov 2005 7:22 AM

Michael Rys [MSFT]

What error do you see? What datatype does @Date have? Note that since the
SQL datetime type does not provide timezones, you cannot use sql:variable on
datetime variables. You have to use a string value, add the timezone and
then cast it to xs:dateTime in the XQuery context.

Best regards
Michael

<St***@somewhere.com> wrote in message
Show quoteHide quote

news:ek5ZcdY6FHA.2576@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I am trying to insert a new element into a typed xml column.
>
> my problem is, how do you insert an element that has an attribute
> containig
> a sql:variable i.e. TimeStamp="sql:variable("@Date")".
> This returns an error.
>
> update Statistics
> set
> EntriesByDate.modify('declare namespace
> s="http://schemas/StatisticsEntriesByDate"; insert <s:Entries
> TimeStamp="sql:variable("@Date")" Total="1" /> as last into
> (/s:EntriesByDate)[1]')
> where SiteId = @SiteId
>
> My solution was to insert the element 'as last' and then update the last
> element in the list, but that doesn't seem like the best way to do this.
>
>
> Thanks
>
> Stephen