Add perent element

I want to add perent element to existing element in xml variable (SQL 2005).
I solve that problem with:
DECLARE @.a xml
SET @.a =
N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c
></EVENT_INSTANCE>'
SELECT @.a
SET @.a.modify('
insert (<AddElement></AddElement> )
after (/EVENT_INSTANCE/a)[1]')
select @.a
SET @.a.modify('
insert /EVENT_INSTANCE/b1
as first
into (/EVENT_INSTANCE/AddElement)[1]')
select @.a
SET @.a.modify('
delete /EVENT_INSTANCE/b1')
SELECT @.a
Is there any simplier solution.
ThanksDear Ana
There are two ways at the moment to insert a new intermediate layer. The one
you show below, the other (which may be worse), is to decompose the tree and
use the FOR XML PATH mode to recompose the tree with the new level in
between.
The three updates below - unlike the FOR XML approach - will only update the
subtrees involved in the updates and not the whole XML document.
We are looking into extending the update language to make this a bit better,
but in general, inserting new levels into an XML tree will always be a
somewhat costly operation since your three steps would have to be executed
in any case (due to the node-id implementation that encodes the position in
the tree).
Best regards
Michael
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@.microsoft.com...
>I want to add perent element to existing element in xml variable (SQL
>2005).
> I solve that problem with:
> DECLARE @.a xml
> SET @.a =
> N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51<
/c></EVENT_INSTANCE>'
> SELECT @.a
> SET @.a.modify('
> insert (<AddElement></AddElement> )
> after (/EVENT_INSTANCE/a)[1]')
> select @.a
>
> SET @.a.modify('
> insert /EVENT_INSTANCE/b1
> as first
> into (/EVENT_INSTANCE/AddElement)[1]')
> select @.a
> SET @.a.modify('
> delete /EVENT_INSTANCE/b1')
> SELECT @.a
> Is there any simplier solution.
> Thanks|||Thanks Michael.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OmzYQl06FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Dear Ana
> There are two ways at the moment to insert a new intermediate layer. The
> one you show below, the other (which may be worse), is to decompose the
> tree and use the FOR XML PATH mode to recompose the tree with the new
> level in between.
> The three updates below - unlike the FOR XML approach - will only update
> the subtrees involved in the updates and not the whole XML document.
> We are looking into extending the update language to make this a bit
> better, but in general, inserting new levels into an XML tree will always
> be a somewhat costly operation since your three steps would have to be
> executed in any case (due to the node-id implementation that encodes the
> position in the tree).
> Best regards
> Michael
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@.microsoft.com...
>|||One way is xquery, optionally combined with update query.
select @.a.query('
for $a in /* return (
element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c}
)
')
Pohwan Han. Seoul. Have a nice day.
"Ana Mihalj" <amihalj@.hotmail.com.false> wrote in message
news:%23Q2O2326FHA.2524@.TK2MSFTNGP10.phx.gbl...
> Thanks Michael.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OmzYQl06FHA.2716@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Han.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:ORUxMN#6FHA.268@.TK2MSFTNGP10.phx.gbl...
> One way is xquery, optionally combined with update query.
> select @.a.query('
> for $a in /* return (
> element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c}
> )
> ')
> --
> Pohwan Han. Seoul. Have a nice day.
> "Ana Mihalj" <amihalj@.hotmail.com.false> wrote in message
> news:%23Q2O2326FHA.2524@.TK2MSFTNGP10.phx.gbl...
The
update
always
the
N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c
></EVENT_INSTANCE>'
>

Add perent element

I want to add perent element to existing element in xml variable (SQL 2005).
I solve that problem with:
DECLARE @.a xml
SET @.a =
N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>'
SELECT @.a
SET @.a.modify('
insert (<AddElement></AddElement>)
after (/EVENT_INSTANCE/a)[1]')
select @.a
SET @.a.modify('
insert /EVENT_INSTANCE/b1
as first
into (/EVENT_INSTANCE/AddElement)[1]')
select @.a
SET @.a.modify('
delete /EVENT_INSTANCE/b1')
SELECT @.a
Is there any simplier solution.
Thanks
Dear Ana
There are two ways at the moment to insert a new intermediate layer. The one
you show below, the other (which may be worse), is to decompose the tree and
use the FOR XML PATH mode to recompose the tree with the new level in
between.
The three updates below - unlike the FOR XML approach - will only update the
subtrees involved in the updates and not the whole XML document.
We are looking into extending the update language to make this a bit better,
but in general, inserting new levels into an XML tree will always be a
somewhat costly operation since your three steps would have to be executed
in any case (due to the node-id implementation that encodes the position in
the tree).
Best regards
Michael
"Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@.microsoft.com...
>I want to add perent element to existing element in xml variable (SQL
>2005).
> I solve that problem with:
> DECLARE @.a xml
> SET @.a =
> N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c></EVENT_INSTANCE>'
> SELECT @.a
> SET @.a.modify('
> insert (<AddElement></AddElement>)
> after (/EVENT_INSTANCE/a)[1]')
> select @.a
>
> SET @.a.modify('
> insert /EVENT_INSTANCE/b1
> as first
> into (/EVENT_INSTANCE/AddElement)[1]')
> select @.a
> SET @.a.modify('
> delete /EVENT_INSTANCE/b1')
> SELECT @.a
> Is there any simplier solution.
> Thanks
|||Thanks Michael.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OmzYQl06FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Dear Ana
> There are two ways at the moment to insert a new intermediate layer. The
> one you show below, the other (which may be worse), is to decompose the
> tree and use the FOR XML PATH mode to recompose the tree with the new
> level in between.
> The three updates below - unlike the FOR XML approach - will only update
> the subtrees involved in the updates and not the whole XML document.
> We are looking into extending the update language to make this a bit
> better, but in general, inserting new levels into an XML tree will always
> be a somewhat costly operation since your three steps would have to be
> executed in any case (due to the node-id implementation that encodes the
> position in the tree).
> Best regards
> Michael
> "Ana Mihalj" <AnaMihalj@.discussions.microsoft.com> wrote in message
> news:48FC0BE4-EEB6-408F-A119-994F0C1A0DEE@.microsoft.com...
>
|||One way is xquery, optionally combined with update query.
select @.a.query('
for $a in /* return (
element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c}
)
')
Pohwan Han. Seoul. Have a nice day.
"Ana Mihalj" <amihalj@.hotmail.com.false> wrote in message
news:%23Q2O2326FHA.2524@.TK2MSFTNGP10.phx.gbl...
> Thanks Michael.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OmzYQl06FHA.2716@.TK2MSFTNGP11.phx.gbl...
>
|||Thanks Han.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:ORUxMN#6FHA.268@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> One way is xquery, optionally combined with update query.
> select @.a.query('
> for $a in /* return (
> element EVENT_INSTANCE {$a/a, element AddElement {$a/b1}, $a/c}
> )
> ')
> --
> Pohwan Han. Seoul. Have a nice day.
> "Ana Mihalj" <amihalj@.hotmail.com.false> wrote in message
> news:%23Q2O2326FHA.2524@.TK2MSFTNGP10.phx.gbl...
The[vbcol=seagreen]
update[vbcol=seagreen]
always[vbcol=seagreen]
the[vbcol=seagreen]
N'<EVENT_INSTANCE><a>CREATE_TYPE</a><b1>2005-10-14T01:58:28.070</b1><c>51</c
></EVENT_INSTANCE>'
>

add leading zero to date column?

Hello All,

None of the solutions I have found in the archives seem to solve my
problem. I have a date column in my tables (stored as a char(10))
which I would like to append a leading zero to for those dates that
start with 9 or lower.

Any ideas?

Thanks,

MikeMyk wrote:
> Hello All,
> None of the solutions I have found in the archives seem to solve my
> problem. I have a date column in my tables (stored as a char(10))

No, you have a char() column in your table that you store a string in
that is supposed to represent a date. It may or may not.

> which I would like to append a leading zero to for those dates that
> start with 9 or lower.

I'd recommend you actually make the column a date data type since that's
what it is for, but you can append like this:

CASE WHEN LEFT(YourColumn, 1) BETWEEN '1' AND '9' THEN '0' + YourColumn END

Now, there are tons of things wrong with what I just wrote in that it
assumes the first column will always be 0-9, which, given your specs
above, isn't garunteed. It assumes that there are no spaces in the first
character position. It assumes that if there is a 1 through 9 that the
length of that string plus the new '0' is still within 10 chars. All in
all, it is a crappy solution.

Make your data column type match your data and this problem goes away.

Zach

> Any ideas?
> Thanks,
> Mike|||Database Modeling Sin # 58 - Confusing Data Presentation with Data
Representation.

If you can't convert the column to its proper datatype (highly , greatly,
strongly, and "i mean it dude!" recommended),
add the following to a trigger on the table to insure that the format of the
string is consistent.

update mytable
set DateLikeCharColumn = IsNull( convert( char(10) , convert( datetime,
DateLikeCharColumn ) , 110 ) , "" )
from mytable
join inserted on mytable.keycolumn = inserted.keycolumn

(substitute 110 with what ever style you need)

"nib" <individual_news@.nibsworld.com> wrote in message
news:304ot7F2qrbinU1@.uni-berlin.de...
> Myk wrote:
>> Hello All,
>>
>> None of the solutions I have found in the archives seem to solve my
>> problem. I have a date column in my tables (stored as a char(10))
> No, you have a char() column in your table that you store a string in that
> is supposed to represent a date. It may or may not.
>> which I would like to append a leading zero to for those dates that
>> start with 9 or lower.
> I'd recommend you actually make the column a date data type since that's
> what it is for, but you can append like this:
> CASE WHEN LEFT(YourColumn, 1) BETWEEN '1' AND '9' THEN '0' + YourColumn
> END
> Now, there are tons of things wrong with what I just wrote in that it
> assumes the first column will always be 0-9, which, given your specs
> above, isn't garunteed. It assumes that there are no spaces in the first
> character position. It assumes that if there is a 1 through 9 that the
> length of that string plus the new '0' is still within 10 chars. All in
> all, it is a crappy solution.
> Make your data column type match your data and this problem goes away.
> Zach
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Mike
>

add an attribute to an existing dimension

Hello experts,

Is there a possibility to add an attribute to an existing dimension? (AS2005)

Right now I solve this problem with delete and create this dimension, but it’s a bad solution because a delete all properties too.

Best regards

Alex

If the dimension is based off a table or view reference in the DSV, right-click the background of the DSV and select Refresh. If you are using a named query, you will need to add the field to the query.

Then, go to the dimension editor. The subset of the DSV displayed in the editor should now show the field. Drag that field to the attribute hierarchy list on the left. You now have a new attribute hierarchy.

Make sure its relationships to other attribute hierarchies are appropriately set.

B.

|||

Hello Bryan

Thank you very much. It’s very easy if you know where you have to click ^^

Have a nice day