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 Keywords to Query Analyzers Syntax

Does anyone know of a way to add *key words* to Query Analyzer's syntax
highlighting feature? I would think that there would be a text/xml file
that could be modified. This certainly can't be hard coded.

Thanks,
BryanI think you are going to be disappointed then. I know of no way to add
keywords in the way you want.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan|||"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:4096bd6a$0$31706$fa0fcedb@.lovejoy.zen.co.uk.. .
> I know of no way to add keywords in the way you want.

Then do you know of another way?|||Bryan Bullard (reply@.to.group.com) writes:
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.

There is no way to change the list of highlighted words. I would actually
guess that the list is hardcoded. After all, QA is to designed against a
certain version of SQL Server, so new keywords are not likely to appear
out of the blue. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bryan,

It seems that the keywords are contained in the file
"../tools/binn/sqllex.dll" -- part of which appears in plain-text. For fun,
I tried the following command from a Linux prompt (after backing up the
file):

sed 's/CHECKALLOC/RICHDILLON/' sqllex.dll > new.dll

... and then renamed the new file. Sure enough, QA stopped highlighing
"CHECKALLOC" in blue. Sadly, it wouldn't highlight my name, though. :-(
There must be something important about the rest of the gobbledygook in that
file ;-)

Rich

"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan|||

Quote:

Originally Posted by Erland Sommarskog

Bryan Bullard (reply@.to.group.com) writes:
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.

There is no way to change the list of highlighted words. I would actually
guess that the list is hardcoded. After all, QA is to designed against a
certain version of SQL Server, so new keywords are not likely to appear
out of the blue. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

It's actually hardcoded, and found this out the hardway when a dll was no longer registered.
the syntax highlighting functionality is done by a dll named sqllex.dll that is typically found in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory;
if you unregister the dll, you'll lose the syntax highlighting completely; re-registering it restores the functionality, which i discovered when uninstalling/reinstalling after a beta of SQL 2005.
because the list of keywords is in the DLL, and not in a file the DLL uses, there's no way to ADD keywords unless you could replace that dll with your own enhanced verison.
HTH.

Add Keywords to Query Analyzers Syntax

Does anyone know of a way to add *key words* to Query Analyzer's syntax
highlighting feature? I would think that there would be a text/xml file
that could be modified. This certainly can't be hard coded.

Thanks,
BryanI think you are going to be disappointed then. I know of no way to add
keywords in the way you want.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan|||"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:4096bd6a$0$31706$fa0fcedb@.lovejoy.zen.co.uk.. .
> I know of no way to add keywords in the way you want.

Then do you know of another way?|||Bryan Bullard (reply@.to.group.com) writes:
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.

There is no way to change the list of highlighted words. I would actually
guess that the list is hardcoded. After all, QA is to designed against a
certain version of SQL Server, so new keywords are not likely to appear
out of the blue. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bryan,

It seems that the keywords are contained in the file
"../tools/binn/sqllex.dll" -- part of which appears in plain-text. For fun,
I tried the following command from a Linux prompt (after backing up the
file):

sed 's/CHECKALLOC/RICHDILLON/' sqllex.dll > new.dll

... and then renamed the new file. Sure enough, QA stopped highlighing
"CHECKALLOC" in blue. Sadly, it wouldn't highlight my name, though. :-(
There must be something important about the rest of the gobbledygook in that
file ;-)

Rich

"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan

Add a xml dataSource in a olap cube

Hi,
I have a question, I have ms sql data base, with lot of data, but I have to add data from a web services, may it be possible ?

The preferred way to accomplish this would be to import the web service information into a database, and then add it to your DSV to make it available to the cube. Take a look at SSIS, as it can communicate with web services, and then write the data to a database.|||SSIS will also allow you to take data from a data source (like a web service) and "push" it directly into an OLAP partition. The advantage of pushing into a relational database first is that if you ever need to do a full process of you OLAP database the data is sitting there and you will not need to hit the webservice.