optimize query - how to make it an "Index seek"

create table t1(a varchar(50) , b varchar(50))

create index i1 on t1(a)
create index i2 on t1(b)

create view v1
as
select * from t1 where isnull(a,b) = 'test'

select * from v1

The above SQL "select * from v1" is doing a table scan.
What do I do to make it perform an index seek ??

TIA

- ForXLDBHi!

This query will make use of index i3:

create index i3 on t1(a,b)

Carsten|||Is it possible to have make an "Index seek" ??|||Just found the following behaviour:

When running:
select * from dbo.v1 => SQL Server uses an index scan

select * from dbo.v1 where a='test' => SQL Server uses an index seek

It's quite strange...|||I do use the following...
select * from dbo.v1 where a='test'

So, it works for me I guess.

Thanks !!|||quite strange...

How so?

There's no predicate...|||I'm not sure. But, it shows "Index Seek" in the plan !!|||Hi Brett,

what confused me is the point, that the DBMS changes the execution plan even if there is basically no difference in the constraint (in this case "a = 'test'").

The definition of v1 already contains this WHERE-clause. And that's why I expect SQL Server to generate the same execution plan.

Carsten

How so?

There's no predicate...|||create table t1(a datetime , b datetime)

create view v1
as
select a,b, isnull(a,b) as c from t1

create index i3 on t1(a,b)

select *
from v1
where c = '01/01/2004'

the above sql is using index scan.....can we make it to use index seek ?

--clean up
--drop table t1
--drop view v1
--drop index t1.i3|||One small thing is that you need to have a bunch of data in the table, before the optimizer thinks about the index. If the data portion of the table consists of a single page, then you will always get a table scan. This is not bad, as it is one read. If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.

A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.|||--Change view to:
alter view v1
as
select * from t1 where a = 'test'
union
select * from t1 where b = 'test'
go

--add 2 indexes and drop existing ones:
drop index t1.i1
drop index t1.i2
create index i3 on t1(a,b)
create index i4 on t1(b,a)|||...If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.This is not true when you deal with queries that do not address fields that are not part of an index.

...A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.Really? Try this using DDL changes from my previous post:

select * from v1 where isnull(a,b)='test'

Optimization gurus: Help with varchar vs. text fields decision

Hi, I'm trying to improve performance for a db that stores messages. The message is currently stored in an ntext field, but when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I don't want to actually limit the message size, but it seems like I might get much better performance using a varchar(3000) field to hold most of the messages, and a separate text field just used for those 3% that really are long. Is this a good idea? If so, is it better to put the Message and LongMessage fields in the same table; or, have a separate table to hold the long messages? If it is in a separate table, it would need to be left joined with the message table each time messages are retrieved.

Also -- I am getting about 700 new messages daily, and right now have over 150,000 messages stored. The vast majority of activity involves new messages. Is this a good situation to look at using horizontal partitioning?

Thanks for any help, I don't really have anyone to discuss this with and it is really helpful to get some other views!!

Are you able to upgrade to SQL2005? VARCHAR(max) would be a simple solution to your problem.

|||

Yay, I'm already on SQL server 2005, so I could use varchar(max) -- now that I've heard of it! Are there performance issues to be aware of with max? Any drawback to a design where the row size will vary wildly from row to row??

|||

Celestine:

when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I calculated wrong -- it is an ntext field, so each char is two bytes, not one byte. Meaning 97% of the messages are actually less than 1500 characters, not 3000. All the messages are in English, so I'm not going to continue using ntext or nvarchar.

|||

Varchar(max) allows rows to span physical blocks, hence no row length restriction. Why not look it up on Books-On_line BOL?

|||

I understand that there is no length restriction, I looked it up right away; thanks for making me aware of the max option. What I am asking about is whether there are performance implications to consider with using varchar(max), when you are hoping to get multiple records to fit on a data page.

|||

With varchar(max) most of the records will be fetched with a single read whereas for ntext, two reads will be required for every record irrespective of its size.

OPENXML with Multiple Namespace

Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
Thanks
Saihi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCd ResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1 data2
(0 row(s) affected)
Thanks
Sai
"Chandra" wrote:

> hi Sai
> what is the message that you see.
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> ---
> *** Sent via Developersdex http://www.examnotes.net ***
>|||It's because ResultData is now in the tempuri namespace and you've added a
node to the path that is now missing in your OPENXML statement. Change your
code to:
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:t="http://tempuri.org/" />'
SELECT * FROM OPENXML(@.idoc,
'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
WITH
(
dat1 varchar(255) 't:dat1',
data2 varchar(255) 't:data2'
)
EXEC sp_xml_removedocument @.idoc
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Sai" <Sai@.discussions.microsoft.com> wrote in message
news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
----
--
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
----
--
Thanks
Sai|||Graeme
Thanks a lot,that was a great solution.
I was trying for it for long time :)
I greately appriciate your time and help.
Sai
"Graeme Malcolm" wrote:

> It's because ResultData is now in the tempuri namespace and you've added a
> node to the path that is now missing in your OPENXML statement. Change you
r
> code to:
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:t="http://tempuri.org/" />'
> SELECT * FROM OPENXML(@.idoc,
> 'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
> WITH
> (
> dat1 varchar(255) 't:dat1',
> data2 varchar(255) 't:data2'
> )
> EXEC sp_xml_removedocument @.idoc
> Cheers,
> Graeme
>
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Sai" <Sai@.discussions.microsoft.com> wrote in message
> news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
> Hi
> I would appriciate if any one could help me the following situation.
> The following code is working fine
> ----
--
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> ----
--
> if I add another tag
> <Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
> after adding the above the code looks as follows
> ----
--
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> ----
--
> Thanks
> Sai
>
>

OPENXML with Multiple Namespace

Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
hi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***
|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCdResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1data2
(0 row(s) affected)
Thanks
Sai
"Chandra" wrote:

> hi Sai
> what is the message that you see.
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||It's because ResultData is now in the tempuri namespace and you've added a
node to the path that is now missing in your OPENXML statement. Change your
code to:
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:t="http://tempuri.org/" />'
SELECT * FROM OPENXML(@.idoc,
'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
WITH
(
dat1 varchar(255) 't:dat1',
data2 varchar(255) 't:data2'
)
EXEC sp_xml_removedocument @.idoc
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Sai" <Sai@.discussions.microsoft.com> wrote in message
news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
|||Graeme
Thanks a lot,that was a great solution.
I was trying for it for long time
I greately appriciate your time and help.
Sai
"Graeme Malcolm" wrote:

> It's because ResultData is now in the tempuri namespace and you've added a
> node to the path that is now missing in your OPENXML statement. Change your
> code to:
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:t="http://tempuri.org/" />'
> SELECT * FROM OPENXML(@.idoc,
> 'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
> WITH
> (
> dat1 varchar(255) 't:dat1',
> data2 varchar(255) 't:data2'
> )
> EXEC sp_xml_removedocument @.idoc
> Cheers,
> Graeme
>
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Sai" <Sai@.discussions.microsoft.com> wrote in message
> news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
> Hi
> I would appriciate if any one could help me the following situation.
> The following code is working fine
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> if I add another tag
> <Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
> after adding the above the code looks as follows
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> Thanks
> Sai
>
>

OpenXML with a schema

Why doesn't this work?
DECLARE @.xml VARCHAR(8000)
DECLARE @.iDoc INT
DECLARE @.Status INT
SELECT @.xml = '
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="DocumentKey1" rs:number="1" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentKey1">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="DocumentKey2" rs:number="2" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentKey2">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="DocumentType" rs:number="3" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentType">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="SourceCompanyCode" rs:number="4" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="SourceCompanyCode">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="StarShipCompanyCode" rs:number="5"
rs:nullable="true" rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="StarShipCompanyCode">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<rs:insert>
<z:row DocumentKey1="172" SourceCompanyCode="SOA"/>
</rs:insert>
</rs:data>
</xml>
'
EXEC @.Status = sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT 'sp_xml_preparedocument status=',@.Status
--SELECT * FROM OPENXML (@.idoc, '/xml',2)
select *
FROM OPENXML (@.idoc, '/xml/data/insert/row',4)
WITH(
DocumentKey1 varchar(30)'@.DocumentKey1'
,DocumentKey2 varchar(30)'@.DocumentKey2'
,DocumentType varchar(30)'DocumentType'
,SourceCompanyCodevarchar(30) '@.SourceCompanyCode'
,StarShipCompanyCodevarchar(30) '@.StarShipCompanyCode'
)
You need to declare the namespaces in sp_xml_preparedocument, and use their
prefixes in the XPath used in the OPENXML function:
EXEC @.Status = sp_xml_preparedocument @.idoc OUTPUT, @.xml, '<root
xmlns:z="#RowsetSchema" xmlns:rs="urn:schemas-microsoft-com:rowset"/>'
SELECT 'sp_xml_preparedocument status=',@.Status
--SELECT * FROM OPENXML (@.idoc, '/xml',2)
select *
FROM OPENXML (@.idoc, '/xml/rs:data/rs:insert/z:row',2)
WITH (
DocumentKey1 varchar(30) '@.DocumentKey1'
,DocumentKey2 varchar(30) '@.DocumentKey2'
,DocumentType varchar(30) 'DocumentType'
,SourceCompanyCode varchar(30) '@.SourceCompanyCode'
,StarShipCompanyCode varchar(30) '@.StarShipCompanyCode'
)
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"John Grant" <johnwendell@.hotmail.com> wrote in message
news:B0134ADC-8D97-480C-A952-BA4862A7F75A@.microsoft.com...
Why doesn't this work?
DECLARE @.xml VARCHAR(8000)
DECLARE @.iDoc INT
DECLARE @.Status INT
SELECT @.xml = '
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="DocumentKey1" rs:number="1" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentKey1">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="DocumentKey2" rs:number="2" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentKey2">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="DocumentType" rs:number="3" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="DocumentType">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="SourceCompanyCode" rs:number="4" rs:nullable="true"
rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="SourceCompanyCode">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:AttributeType name="StarShipCompanyCode" rs:number="5"
rs:nullable="true" rs:writeunknown="true" rs:basecatalog="StarShipServer"
rs:basetable="ShipmentKey" rs:basecolumn="StarShipCompanyCode">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="30"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<rs:insert>
<z:row DocumentKey1="172" SourceCompanyCode="SOA"/>
</rs:insert>
</rs:data>
</xml>
'
EXEC @.Status = sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT 'sp_xml_preparedocument status=',@.Status
--SELECT * FROM OPENXML (@.idoc, '/xml',2)
select *
FROM OPENXML (@.idoc, '/xml/data/insert/row',4)
WITH (
DocumentKey1 varchar(30) '@.DocumentKey1'
,DocumentKey2 varchar(30) '@.DocumentKey2'
,DocumentType varchar(30) 'DocumentType'
,SourceCompanyCode varchar(30) '@.SourceCompanyCode'
,StarShipCompanyCode varchar(30) '@.StarShipCompanyCode'
)

OPENXML question

Can't get the following code to return two rows:
DECLARE @.data varchar(1000)
DECLARE @.hdoc int
select @.data = '<root><guid>a1</guid><guid>b1</guid></root>'
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.data
select *
from openxml(@.hdoc, '/root', 2)
with (guid varchar(10))
EXEC sp_xml_removedocument @.hdoc
Because you have only one root element you will only get one row.
Use
select *
from openxml(@.hdoc, '/root/guid', 2)
with (guid varchar(10) '.')
HTH
Michael
"Mark Frishman" <mfrishman_nospam@.hotmail.com> wrote in message
news:%23Bcjk7lfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Can't get the following code to return two rows:
> DECLARE @.data varchar(1000)
> DECLARE @.hdoc int
> select @.data = '<root><guid>a1</guid><guid>b1</guid></root>'
> EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.data
> select *
> from openxml(@.hdoc, '/root', 2)
> with (guid varchar(10))
> EXEC sp_xml_removedocument @.hdoc
>

OPENXML problem with xmlns:xsd

The following works fine and gives expected result:
DECLARE @.doc varchar(8000)
SET @.doc ='
<ScheduleDefinition>
<WlyRecurrence >
<WsInterval>1</WsInterval>
<DaysOfW>
<Tuesday>true</Tuesday>
</DaysOfW>
</WlyRecurrence>
</ScheduleDefinition>
'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;
SELECT * FROM
OPENXML (@.idoc, '/ScheduleDefinition/WlyRecurrence', 2) WITH
(WsInterval int, DaysOfW xml)
--query result
WsInterval DaysOfW
1 <DaysOfW><Tuesday>true</Tuesday></DaysOfW>
But, when @.doc is changed (just added xmlns to the nodes) as follows:
<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/
2006/03/15/reporting/reportingservices">2006-08-14T03:25:00.000-05:00</
StartDateTime>
<WlyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/
2006/03/15/reporting/reportingservices">
<WsInterval>1</WsInterval>
<DaysOfW>
<Tuesday>true</Tuesday>
</DaysOfW>
</WlyRecurrence>
</ScheduleDefinition>
the query doesn't work. How can I get the same results? What am I
missing? Thanks in advance.Changing the call to the stored proc sp_xml_preparedocument (by adding
the xpath_namespaces) doesn't work either:
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<ScheduleDefinition
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://
www.w3.org/2001/XMLSchema-instance" />';
In addition to the above change, I need to delete the xmlns attribute
from the WlyRecurrence element to make it work.
before: <WlyRecurrence xmlns="http://schemas.microsoft.com/
sqlserver/2006/03/15/reporting/reportingservices">
after: <WlyRecurrence >
However, I can't remove the xml namespace from the WlyRecurrence
element. Given that, how do I get the results I mentioned in the
previous post?
Thanks.
On Dec 6, 4:17 pm, preddy <prvang...@.gmail.com> wrote:
> The following works fine and gives expected result:
> DECLARE @.doc varchar(8000)
> SET @.doc ='
> <ScheduleDefinition>
> <WlyRecurrence >
> <WsInterval>1</WsInterval>
> <DaysOfW>
> <Tuesday>true</Tuesday>
> </DaysOfW>
> </WlyRecurrence>
> </ScheduleDefinition>
> '
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;
> SELECT * FROM
> OPENXML (@.idoc, '/ScheduleDefinition/WlyRecurrence', 2) WITH
> (WsInterval int, DaysOfW xml)
> --query result
> WsInterval DaysOfW
> 1 <DaysOfW><Tuesday>true</Tuesday></DaysOfW>
> But, when @.doc is changed (just added xmlns to the nodes) as follows:
> <ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/
> 2006/03/15/reporting/reportingservices">2006-08-14T03:25:00.000-05:00</
> StartDateTime>
> <WlyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/
> 2006/03/15/reporting/reportingservices">
> <WsInterval>1</WsInterval>
> <DaysOfW>
> <Tuesday>true</Tuesday>
> </DaysOfW>
> </WlyRecurrence>
> </ScheduleDefinition>
> the query doesn't work. How can I get the same results? What am I
> missing? Thanks in advance.|||Look in BOL for WITH XMLNAMESPACES, you have a default namespace on the
data.
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"preddy" <prvangala@.gmail.com> wrote in message
news:bb4e9b29-6b0a-433c-802a-f63d64a7ad74@.l1g2000hsa.googlegroups.com...
> Changing the call to the stored proc sp_xml_preparedocument (by adding
> the xpath_namespaces) doesn't work either:
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<ScheduleDefinition
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://
> www.w3.org/2001/XMLSchema-instance" />';
> In addition to the above change, I need to delete the xmlns attribute
> from the WlyRecurrence element to make it work.
> before: <WlyRecurrence xmlns="http://schemas.microsoft.com/
> sqlserver/2006/03/15/reporting/reportingservices">
> after: <WlyRecurrence >
> However, I can't remove the xml namespace from the WlyRecurrence
> element. Given that, how do I get the results I mentioned in the
> previous post?
> Thanks.
>
> On Dec 6, 4:17 pm, preddy <prvang...@.gmail.com> wrote:
>

OPENXML problem with xmlns:xsd

The following works fine and gives expected result:
DECLARE @.doc varchar(8000)
SET @.doc ='
<ScheduleDefinition>
<WeeklyRecurrence >
<WeeksInterval>1</WeeksInterval>
<DaysOfWeek>
<Tuesday>true</Tuesday>
</DaysOfWeek>
</WeeklyRecurrence>
</ScheduleDefinition>
'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;
SELECT * FROM
OPENXML (@.idoc, '/ScheduleDefinition/WeeklyRecurrence', 2) WITH
(WeeksInterval int, DaysOfWeek xml)
--query result
WeeksIntervalDaysOfWeek
1<DaysOfWeek><Tuesday>true</Tuesday></DaysOfWeek>
But, when @.doc is changed (just added xmlns to the nodes) as follows:
<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/
2006/03/15/reporting/reportingservices">2006-08-14T03:25:00.000-05:00</
StartDateTime>
<WeeklyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/
2006/03/15/reporting/reportingservices">
<WeeksInterval>1</WeeksInterval>
<DaysOfWeek>
<Tuesday>true</Tuesday>
</DaysOfWeek>
</WeeklyRecurrence>
</ScheduleDefinition>
the query doesn't work. How can I get the same results? What am I
missing? Thanks in advance.
Changing the call to the stored proc sp_xml_preparedocument (by adding
the xpath_namespaces) doesn't work either:
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<ScheduleDefinition
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://
www.w3.org/2001/XMLSchema-instance" />';
In addition to the above change, I need to delete the xmlns attribute
from the WeeklyRecurrence element to make it work.
before: <WeeklyRecurrence xmlns="http://schemas.microsoft.com/
sqlserver/2006/03/15/reporting/reportingservices">
after: <WeeklyRecurrence >
However, I can't remove the xml namespace from the WeeklyRecurrence
element. Given that, how do I get the results I mentioned in the
previous post?
Thanks.
On Dec 6, 4:17 pm, preddy <prvang...@.gmail.com> wrote:
> The following works fine and gives expected result:
> DECLARE @.doc varchar(8000)
> SET @.doc ='
> <ScheduleDefinition>
> <WeeklyRecurrence >
> <WeeksInterval>1</WeeksInterval>
> <DaysOfWeek>
> <Tuesday>true</Tuesday>
> </DaysOfWeek>
> </WeeklyRecurrence>
> </ScheduleDefinition>
> '
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;
> SELECT * FROM
> OPENXML (@.idoc, '/ScheduleDefinition/WeeklyRecurrence', 2) WITH
> (WeeksInterval int, DaysOfWeek xml)
> --query result
> WeeksInterval DaysOfWeek
> 1 <DaysOfWeek><Tuesday>true</Tuesday></DaysOfWeek>
> But, when @.doc is changed (just added xmlns to the nodes) as follows:
> <ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/
> 2006/03/15/reporting/reportingservices">2006-08-14T03:25:00.000-05:00</
> StartDateTime>
> <WeeklyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/
> 2006/03/15/reporting/reportingservices">
> <WeeksInterval>1</WeeksInterval>
> <DaysOfWeek>
> <Tuesday>true</Tuesday>
> </DaysOfWeek>
> </WeeklyRecurrence>
> </ScheduleDefinition>
> the query doesn't work. How can I get the same results? What am I
> missing? Thanks in advance.
|||Look in BOL for WITH XMLNAMESPACES, you have a default namespace on the
data.
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"preddy" <prvangala@.gmail.com> wrote in message
news:bb4e9b29-6b0a-433c-802a-f63d64a7ad74@.l1g2000hsa.googlegroups.com...
> Changing the call to the stored proc sp_xml_preparedocument (by adding
> the xpath_namespaces) doesn't work either:
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<ScheduleDefinition
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://
> www.w3.org/2001/XMLSchema-instance" />';
> In addition to the above change, I need to delete the xmlns attribute
> from the WeeklyRecurrence element to make it work.
> before: <WeeklyRecurrence xmlns="http://schemas.microsoft.com/
> sqlserver/2006/03/15/reporting/reportingservices">
> after: <WeeklyRecurrence >
> However, I can't remove the xml namespace from the WeeklyRecurrence
> element. Given that, how do I get the results I mentioned in the
> previous post?
> Thanks.
>
> On Dec 6, 4:17 pm, preddy <prvang...@.gmail.com> wrote:
>

openxml limitation

I am passing xmlstring as varchar(8000) to be used inside the openxml query.
At present I am using sql server 2000.
The issue is that some of the xmlstrings are longer than 8000 characters.
Tried using text but can not manipulate strings using text datatypes.

What is the alternative please?

You could upgrade to SQL Server 2005 to take advantage of the new VARCHAR(MAX) datatype.

Chris

|||

Any suggestions using sql server 2000?

Thanks