I need to associate to a record a variable number and types of fields.I
can't know what they will be, so the solution I'll use must be flexible.
I need to use SQL Server 2000 and .NET 1.1.

Is there any way to user SQL Server in efficient way (not a text data type
and LIKE '<mynode>myvalue</mynode> searches)?
Is it better a text field or a binary? Fulltext search engine can help me to
search values? Other approches, with or without xml involved?

Or I need to wait for SQL Server 2003 and its "xml" data type because today
there isn't a good solution?

thanks

Hi Trapulo,

Thanks for your post. According to your description, I am not quite clear
what your concern is. Do you mean that you want to filter the records with
the variables for which the data type of the parameters is unknown? If I
have misunderstood, please feel free to let me know. So far as I know, it
seems hard to meet your requirements in SQL Server. You have to define the
data type of your variables beforehand.

Thanks for participating the community.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hello,
I need to create a flexible data structure, and I'm thinking to xml.

Eg.
Data Type 1
field1 as int16
field2 as int32
cust1 as varchar
cust2 as varchar

DataType 2
field1 as int16
field2 as int32
cust1 as int16
cust2 as char
cust3 as bit

and so on. And I don't know the number of data types and the fields of each
type.

My idea was to define a table as this:
Table
field1 as int16
field2 as int32
XMLAttribs as text

Is this the best solution I can use?
Is there any way to filter rows basing on data in xmlAttribs field? If no,
is it better if I use a binary type for xmlAttribs?
Is there other way to solve the problem?

thanks

Hi Trapulo,

Thanks for your feedback. According to your description, I understand that
you want to import data having different data types to the Table in the SQL
Server with the same data format. If I have misunderstood you, please feel
free to let me know.

Based on my experience, it seems that there is not an easy way to meet your
requirements in regards to the SQL Server. The Data in Database needs
regular storage format. It is possible that you can force storing the data
having different data types to the table via heavy work, but you need to
pay more attention to maintain this table. Also, when you want to retrieve
the data from the table, is the data in the table still significative as
the one before? It is appreciated that you can tell me your root concerns.

Thanks for posting in community.

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

""Yuan Shao"" <v-yshao (AT) online (DOT) microsoft.com> wrote

You are right.
I need to create a catalog of object, with some attributes standard and some
attributes personalized for each type of object loaded into the catalog.
Without limit about number and type of this objects (if I haven't this
requirement, I can use some table with one-to-one relationship).

I need to catalog data with a flexible description schema. My primary
information are registered inside "normal" relational fields. The flexible
fields are the problem. I need some search features about this fields, but
they are not the primary search keys. If I want only store and retrieve
extended information, I'll use a text field to store xml data and then the
application can understand it. But I need to make some search on data
contained in this fields...

The best solution I've found until today is XML datatype field in SQL Server
2003 Yukon, that support xpath filters on xml fields and can "decode" the
string inside the field to perform search as a normal "where" statement. But
I can't have SQL 2003, so I need an alternative solution...

Thanks

Hi Trapulo,

Thanks for your update. Please check to see if sqlverflow-field can meet
your requirements.

In a schema, you can identify a column as an overflow column to receive all
unconsumed data from the XML document. This column is specified in the
schema by using the sqlverflow-field annotation. It is possible to have
multiple overflow columns.

For more information regarding sqlverflow-field, please refer to the
following article:
sqlverflow-field
http://msdn.microsoft.com/library/de...us/sqlxml3/htm
/bulkload_19m4.asp

Thanks for posting in the community.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Yes, this can load variable-schema data into varchar field (however I can
make it anyway.. I load data with a stored procedure and not via SQLXML).
But then, how can I filter rows basing on data inside OverflowColumn ? I
think I cannot...

At this point I think there isn't solution to my problem with sql server..

Thanks

If the overflow data is less than 8000 characters (4000 nvarchar), you can
use OpenXML to get information back out in SQL Server 2000. But for full
access, you need to wait for Yukon...

Best regards
Michael

"Trapulo" <nonscrivermi (AT) qui (DOT) it> wrote

"Michael Rys [MSFT]" <mrys (AT) online (DOT) microsoft.com> wrote


Excuse me, what is openXML?

Hello,

Thanks for your update. OpenXML is a SQL Server 2000 extension to
Transact-SQL that allows stored procedures to process XML and generate
rowsets from the data for use by Transact-SQL statements.

I found the following article for your reference.
Writing XML Using OPENXML
http://msdn.microsoft.com/library/de...us/xmlsql/ac_o
penxml_94mk.asp

OPENXML
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
oa-oz_5c89.asp

Thanks for posting in community.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.