How to create Schema with FOR XML EXPLICIT option?

Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
'' [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for=3D"nome">Nome</label>
<label for=3D"cognome">Cognome</label>
<label for=3D"eta">Et=E0</label>
<input id=3D"1" type=3D"input" name=3D"nome" value=3D"" />
<input id=3D"2" type=3D"input" name=3D"cognome" value=3D"" />
<input id=3D"3" type=3D"input" name=3D"eta" value=3D"" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?Hello maurox,

> the XMLSCHEMA option don't work with EXPLICIT mode right?
Nope, not yet anyway. If you can save the query results to a file and if
you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||thank kent.

>Nope, not yet anyway. If you can save the query results to a file and if
>you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
it is the only solution?
i try with xsd.exe but i don't know how set the type of each
attribute..(i.e. attribute 'id' is integer but in the schema generated
is string)...sql

How to create Schema with FOR XML EXPLICIT option?

Hi,
i have this query:
SELECT 1 as Tag, NULL as Parent,
nomecampo as [label!1!for],
etichetta as [label!1!],
null as [input!2!id],
null as [input!2!type],
null as [input!2!name],
null as [input!2!value]
FROM t_campiaree
UNION ALL
select 2 as tag,
0 as parent,
null as [label!2!for],
null as [label!2!],
id as [input!2!id],
tipointerfaccia as [input!2!type],
nomecampo as [input!2!name],
'' [input!2!value]
from t_campiaree
FOR XML EXPLICIT
and the results is this:
<label for=3D"nome">Nome</label>
<label for=3D"cognome">Cognome</label>
<label for=3D"eta">Et=E0</label>
<input id=3D"1" type=3D"input" name=3D"nome" value=3D"" />
<input id=3D"2" type=3D"input" name=3D"cognome" value=3D"" />
<input id=3D"3" type=3D"input" name=3D"eta" value=3D"" />
now, how i can create schema for this xml file?
the XMLSCHEMA option don't work with EXPLICIT mode right?
Hello maurox,

> the XMLSCHEMA option don't work with EXPLICIT mode right?
Nope, not yet anyway. If you can save the query results to a file and if
you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||thank kent.

>Nope, not yet anyway. If you can save the query results to a file and if
>you have the .NET SDK installed, you can use XSD.EXE to infer a schema.
it is the only solution?
i try with xsd.exe but i don't know how set the type of each
attribute..(i.e. attribute 'id' is integer but in the schema generated
is string)...

How to create PK in duplicate column?

Hi,

How to create PK in duplicate column?

for example:

id fname lname age departments

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

...

I want to create PK on fname column.

you can't...how would the foreign key know where to link to....row 1 or row 2 or row 3 or all of them?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Primary Keys are by definition "unique". You cannot have duplicates.

From your example, a index on fname is what you want.|||

Thanks,

So the question becomes "How to delete duplicate records"?

|||

fastest way would be (change realtable to your table name)

select distinct * into backuptable

from realtable

truncate realtable

insert into realtable

select * from backuptable

drop table backuptable

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi,

the results are same when using select distinct * from table1

select distinct * from table1

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

|||

The answer i gave you won't work because of the id column and the rest is not unique either

what the code below does is get the min id per fname and dumps it into a temp table

then we delete from the real table everything that is not in the temp table

I used min, you can use max

select min(id),fname into #temp
from YourTable
group by fname

delete YourTable
where not exists (select * from #temp where YourTable.id = #temp.id)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

you are only allowed one PK per table! you can have multiple columns as a primary key however.

sql server2000 wrote:

Hi,

How to create PK in duplicate column?

for example:

id fname lname age departments

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

...

I want to create PK on fname column.

How to create multiple selects if the preceeding value is NULL

Help! I am trying to create a query that will perform 'Fallback' queries if
the preceeding value is NULL. and then change the result to display fixed
text (like using the CASE statement) eg:
The first select is select name from guest where room='123' and status='I'
If there are no names found, I want it to run the next statement
select code from OOO where room='123' .If there are results for this, I
would like SQL to change the result 'code' and put text 'OUT OF ORDER'.
If there are no results for the first or second selects, to put the text
'VACANT' in the field.
I am using curse and @.DECLARES, where the room will be declared with the
curse.
The Columns and tables are;
Table Column
Z_rooms code varchar(6)
guest name varchar (70)
outoforder code varchar (6)
At the moment it looks something like this, albeit an abridged version
DECLARE @.guest_name varchar (70)
DECLARE @.room_code varchar (6)
declare curse cursor
for select code from z_rooms where property='NEWCH' (This will declare the
room_code
open curse
fetch next from curse into @.room_code
while (@.@.fetch_status<>-1)
begin
if (@.@.fetch_status<>-2)
begin
Output should look like
room guest_name
101 MOUSE, MR MICKEY (if there is a name, this is an exampl
e)
101 OUT OF ORDER (if there is no guest name, but OOO code is
not null)
101 VACANT (if there is no guest name OR OOO code found)
I hope this helps clarify what I am trying to doWhy are you using a cursor to do this? Always try to avoid cursors. It
looks like a single SELECT statement will give the result you want:
SELECT room,
COALESCE(guest_name,
CASE
WHEN outoforder.code IS NOT NULL
THEN 'OUT OF ORDER'
WHEN outoforder.code IS NULL
THEN 'VACANT'
END)
FROM z_rooms
JOIN guest
ON ? /* unspecified */
JOIN outoforder
ON ? /* unspecified */
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Here is a wild guess:
SELECT room_nbr,
CASE WHEN guest_name IS NOT NULL THEN guest_name
WHEN guest_name IS NULL AND room_status = 0
THEN 'out of order'
ELSE 'vacant' END AS guest_or_room_status
FROM Hotel
WHERE room_nbr = @.my_room;
You might want to read a book on data modeling so that you will not
have a column drawn from two different domains in a table. And none
of those names were very helpful in any case.

How to create hash function for NTEXT

I have a table
create table t
(
val ntext(16),
id int not null identity,
hash nvarchar(32)
)
I'd like to avoid rows with double ntext values, thats why I want to compare
hast values only.
But how can I create hash for ntext ?
ThanksOleg,
You could try using checksum_agg after chunking the ntext column
into 8000 byte pieces. Here's an example using image (there is a long
image column in a Northwind table that makes it easier to write a repro).
checksum() and checksum_agg() are not good hash functions, but they
will help you identify rows that may be the same. You will still have to
check to see if they are the same, as you would with any hash function.
create table Integers (
chunk int primary key
)
insert into Integers select ProductID-1 from Northwind..Products
go
create table textVals (
pk int identity primary key,
t image,
hashVal int
)
insert into textVals select photo, null from Northwind..Employees
go
update textVals set
hashVal = (
select checksum_agg(ca) from (
select checksum(substring(tv.t,1+8000*chunk,8000)) as ca
from Integers, textVals as tv
where tv.pk = textVals.pk and chunk <= datalength(t)/8000
) T
)
select datalength(t) as t_len, hashVal from textVals
go
drop table Integers, textVals
-- Steve Kass
-- Drew University
Oleg Cherkasenko wrote:
>I have a table
>create table t
>(
>val ntext(16),
>id int not null identity,
>hash nvarchar(32)
>)
>I'd like to avoid rows with double ntext values, thats why I want to compare
>hast values only.
>But how can I create hash for ntext ?
>Thanks
>
>