query xml datatype

In SQL 2005, we've defined a column as type "xml". We'd like to query
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?
You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegro ups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>

query xml datatype

In SQL 2005, we've defined a column as type "xml". We'd like to query
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegroups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>

Query which system table to answer this

I have a DB with 1 default defined: UW_Zerodefault
It simply puts a 0 into particular fields upon new record creation.
Is there a query I can run against a particular system table to give me a list of fields this default is applied against in the DB?
ThanksSqlSpec will give you this information, but I can't remember now what table it's hitting to get it. it may be as simple as querying sysdepends.

I'll take a look at the code tonight and post again.|||If I understand your query correctly (It's late and I'm tired!!) you can run exec sp_mshelpcolumns 'enter table name' and the col_dridefname column is the contraint name and the first text column is the default value.|||this give you all default usage by columns:

select
s.name as colname
,o1.name as tablename
,o2.name as defaultname
from
syscolumns s
inner join
sysobjects o1 on o1.id=s.id
inner join
sysobjects o2 on o2.id=s.cdefault

and this will give you default usage by udts:

exec sp_mshelptype @.typename=null, @.flags='uddt'

Query value in between two columns

Hi All,

I am new to Sql and I want to know if a given value lets say its 225
is within ranges defined in the table.

I have a table TblControl. Data type of both field is int.

StartRange End Range
0 100
200 300
500 600
900 950

(Sql server 2000 on window 2000)

Thanks
-MaxOn 26 Aug 2004 12:13:48 -0700, Max wrote:

>Hi All,
>I am new to Sql and I want to know if a given value lets say its 225
>is within ranges defined in the table.
>I have a table TblControl. Data type of both field is int.
>StartRange End Range
>0 100
>200 300
>500 600
>900 950
>(Sql server 2000 on window 2000)
>Thanks
>-Max

Hi Max,

You can test this with BETWEEN. Example:

SELECT StartRange, EndRange
FROM Control
WHERE 225 BETWEEN StartRange AND EndRange

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,

It solved my problem.

-Maxsql

query to obtain linked server properties

Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers already
Hi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj

query to obtain linked server properties

Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj

query to obtain linked server properties

Im trying to find the table/column that stores the linked server security
property info such as
For a login not defined in the list above, connections will:
Not be made
Be made without using a security context
Be made using the logins security context
Be made using this security context ..
I want to generate a query that will give me a list of all linked servers
defined along with these settings . Also what Server type it is..whether its
a SQL Server or another data source.. I looked at sysservers alreadyHi Hassan,
Check out 'sp_helplinkedsrvlogin' That gives you what you are looking
for.
Regards,
-Manoj

Query Sysindexes

i hav a table, Test1, with a clustered index and two non clustered index defined on it.

When i use sp_help Test1, the CI and non clustered index are listed along with the column names.

when i query the sysindex table (for id = object_id(Test1))

i can see entries like _WA_Sys_<<ColumnName>>_3D5EEB29.

what are these entries? are they indexes? if yes, how these entries are created and what is the significance of these entries.

Pl discuss.

Thanks.Hi

Given the info - why would you not expect to see this entry? Did you explicitly name your indexes? If not the SQL Server generates names for you. I admit I can't remember the conventions it uses (I name my indexes).

Also, SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason.|||Hello.

Thanks for the reply.

i hav named my indexes. the entries i mentioned are displayed in addition to the indexes i created.

"SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason." -- could u please elaborate on this or let me know where i can find more info.

Thanks|||Entries like _WA_Sys... are for Statics.|||Entries like _WA_Sys... are for Statics.Thanks mihirclarion. http://www.google.com/custom?cx=013269018370076798483:gg7jrrhpsy4&cof=GFNT%3A%23666666%3BGALT%3A%23666666%3BLH%3A100 %3BCX%3ABlackle%3BVLC%3A%23999999%3BDIV%3A%2300000 0%3BFORID%3A1%3BT%3A%23999999%3BALC%3A%23cccccc%3B LC%3A%23cccccc%3BS%3Ahttp%3A%2F%2Fwww%2Eblackle%2E com%2F%3BL%3Ahttp%3A%2F%2Fwww%2Eheapmedia%2Ecom%2F blackle%2Flogo%2Ejpg%3BGIMP%3A%23666666%3BLP%3A1%3 BBGC%3A%23000000%3BAH%3Aleft&q=_WA_Sys&hl=en&client=pub-8993703457585266

Coo - that's a long URL.


"SS enforces unique constraints as indexes so you could get more entries in sysindexes than you expect for this reason." -- could u please elaborate on this or let me know where i can find more info.
BoL is the source of all knowledge:
http://msdn2.microsoft.com/en-us/library/ms177420.aspx|||Thank you :)|||_WA_Sys is auto generated statistics. If you execute a query and one (or more) of the columns in the WHERE clause does not have an index or an appropriate statistics, the SQL Server will generate statistics on the column for you. The reason it is named _WA_Sys is because it is system generated and programmed by the SQL Server developers sitting in Washington.

Example:

create table test(id int)
insert into test values(1)

-- SQL Server 2005: This query will now return 0 rows because no
-- statistics has been created for any column in the table
select s.name,o.name
from sys.stats s
inner join sys.objects o on s.object_id=o.object_id
where s.name like '_WA%' and o.name='test'

select id from test where id=1

-- This time this query will return one row because a statistics
-- was auto generated on the id column.
select s.name,o.name
from sys.stats s
inner join sys.objects o on s.object_id=o.object_id
where s.name like '_WA%' and o.name='test'

If you're using SQL Server 2000, use this statistics query instead:

select s.name,o.name from sysindexes s
inner join sysobjects o on s.id=o.id
where s.name like '_WA%' and o.name='test'