Best query||design for table>subtable

Hi,

I've had few times the same problem and i'd like to go over it.
I'm facing this kind of data:

say that i need to store documents, all of them have a set of fields in common and all also have specific fields:

articles [ TITLE, AUTHOR, DATE, PIC, EXTRACT, KEYWORDS, ... ]
news [ TITLE, AUTHOR, DATE, URL, SOURCE, KEYWORDS, ... ]
...

my first solution was to store all of this data like this:
doc [ TITLE, AUTHOR, DATE, KEYWORDS, TABLENAME, TABLEID ]
doc_articles [ ID, PIC, EXTRACT ]
doc_news [ID, URL, SOURCE ]

It works fine BUT i need 2 queries for each doc to get its corresponding specific data.

Is there a way to dynamicaly link the corresponding fields leading to a result set with all table fields??

Is there another way to design tables to make it easier?

Thx in advance for reading,

++Check to see how your database engine supports the JOIN operation. I think it will do what you want.

-PatP|||Yes, i may not have been precise enough,

I actually JOIN when i know TABLENAME:

If i want news rows i do a

select * from doc,doc_news left join doc.TABLEID=doc_news.ID where doc.TABLENAME=doc_news

BUT i sometimes get a doc row without knowing if its a news or an article... i then need a 2nd query to get the specific table rows.

Would there be a query to select the doc row AND the specific table row with one query?

I'd say, Is there dynamic SQL possilities like

SELECT * from doc,doc.TABLENAME where doc.ID=X ??
(doesn't work "as is" in mysql)...

++|||use LEFT OUTER JOINs to join the doc table to both the news and the articles table

each PK in doc will have only one matching FK in either news or articles, so bob's your uncle -- still only one query!!

:)

but check your syntax carefully, because this isn't even close to being valid syntax --

select * from doc
,doc_news
left join doc.TABLEID=doc_news.ID
where doc.TABLENAME=doc_news|||if you must continue with your current design you could also consider aliasing
eg
select Article.docID as ArtDoc, Article.Author as ArtAuthor from blah

not nice but it may help

best place to declare/load XSL for use in CLR StoredProc?

How to efficiently load XSL documents which will be used in a CLR SP. I want
to avoid loading it every time the SP is invoked.
Thanks,
ChrisHello ChrisHarrington" charrington-at-activeinterface.com,

> How to efficiently load XSL documents which will be used in a CLR SP.
> I want to avoid loading it every time the SP is invoked.
How about in an table, passing it as a parameter?
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Kent,
Thanks for responding. This CLR SP stuff in new to me. Could you elaborate
on your suggestion? Do you mean storing the XSL in an XML column in a table?
Thanks,
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7452758c85c8a7518f6a0@.news.microsoft.com...
> Hello ChrisHarrington" charrington-at-activeinterface.com,
>
> How about in an table, passing it as a parameter?
> --
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||> Thanks for responding. This CLR SP stuff in new to me. Could you
> elaborate on your suggestion? Do you mean storing the XSL in an XML column
in a
> table?
I'll post an example in my blog shortly.
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks - that would be very helpful.
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7454618c85ce91388c3f0@.news.microsoft.com...
> in a
> I'll post an example in my blog shortly.
>
> --
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hello Chris,
Been a bit too busy to post, but here's the gist of it. First, we need a
SQLCLR function that actually does the transformation. Here's that:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
namespace DM.Examples
{
public partial class XmlLibrary
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic =
false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
[return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize
= -1)]
public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
{
// on null return null, just in case.
if (Data.IsNull || StyleSheet.IsNull)
return SqlXml.Null;
// Buffer the transformed xml
MemoryStream ms = new MemoryStream();
XmlWriter xw = XmlWriter.Create(ms);
// Load and transform
XslCompiledTransform ctx = new XslCompiledTransform(false);
ctx.Load(StyleSheet.CreateReader());
ctx.Transform(Data.CreateReader(), xw);
// return the result, assuming XML compliant output
return new SqlXml(ms);
}
}
}
here's some code I wrote to test that:
declare @.d xml, @.s xml
select @.d = (select productID as '@.dbid',ProductNumber as '@.productID',Name
as 'name',Color as 'color',ListPrice as 'listPrice',Size as 'Size',SizeUnitM
easureCode
as 'sizeCode',style as 'style' from adventureworks.production.product where
not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag =
1 for xml path('product'),root('products'),element
s xsinil,type)
select @.s = bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_clob)
as p
select dbo.ApplyTransform(@.d,@.s)
the "c:\simple.xlst" is left as an excercise for the reader.
Cheers,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thanks for the code sample. But what I am really stumped on is how to have
the xsl available as a class static so that it doesn't have to be loaded and
compiled every time the SP is invoked. Any thoughts?
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad745b3b8c85df2965c9c40@.news.microsoft.com...
> Hello Chris,
> Been a bit too busy to post, but here's the gist of it. First, we need a
> SQLCLR function that actually does the transformation. Here's that:
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Xml;
> using System.Xml.Xsl;
> using System.IO;
> namespace DM.Examples
> {
> public partial class XmlLibrary
> {
> [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic =
> false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
> [return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize
> = -1)]
> public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
> {
> // on null return null, just in case.
> if (Data.IsNull || StyleSheet.IsNull)
> return SqlXml.Null;
> // Buffer the transformed xml
> MemoryStream ms = new MemoryStream();
> XmlWriter xw = XmlWriter.Create(ms);
> // Load and transform
> XslCompiledTransform ctx = new XslCompiledTransform(false);
> ctx.Load(StyleSheet.CreateReader());
> ctx.Transform(Data.CreateReader(), xw);
> // return the result, assuming XML compliant output
> return new SqlXml(ms);
> }
> }
> }
> here's some code I wrote to test that:
> declare @.d xml, @.s xml
> select @.d = (select productID as '@.dbid',ProductNumber as
> '@.productID',Name as 'name',Color as 'color',ListPrice as 'listPrice',Size
> as 'Size',SizeUnitMeasureCode as 'sizeCode',style as 'style' from
> adventureworks.production.product where
> not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag = 1
> for xml path('product'),root('products'),element
s xsinil,type)
> select @.s = bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_clob)
> as p
> select dbo.ApplyTransform(@.d,@.s)
> the "c:\simple.xlst" is left as an excercise for the reader.
> Cheers,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>

beginner in SQL Server 2000

Hi,
i'm a beginner in sql server 2000 and i have a question.
i want to store word, excel, pdf etc documents in order to search them
for specific content. i don't want to get as result lines of the
documents but the names of them.What data type i must use? can i search
and for Metadata information and how? i will really appreciate any
answer.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
use the image datatype.
Have a look at
http://www.indexserverfaq.com/blobs.htm for more information on how to do
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Bill,
Yes, you can store word, excel, pdf etc documents in order to search them
for specific content (as in Full-text) the contents of the MS Word files
stored in an SQL Table's FT-enable IMAGE column. As you're are using SQL
Sever 2000, you will need to add a "file extension" column to your table and
populate it with the file extension value, for example "doc" or ".doc" for
MS Word documents. Note, this "file extension" column must be defined as
sysname, char(3) or varchar(4) in order for the MSSearch service to
correctly identify the file type to be indexed.
Additionally, below is a SQL script example of using TextCopy.exe (ships
with SQL Server 2000) to import MS Word documents into SQL Sever table and
IMAGE column and then using SQLFTS CONTAINS or FREETEXT to search the
contents of that MS word document:
use pubs
go
if exists (select * from sysobjects where id = object_id('FTSTable'))
drop table FTSTable
go
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol char(3) NULL, -- can be either sysname or char(3)
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Insert data... (Note: Initalizing IMAGE column with 0xFFFFFFFF for use
with TextCopy.exe)
INSERT FTSTable values('Test TEXT Data for row 1', 0xFFFFFFFF, 'doc', NULL)
go
-- Select data
SELECT * from FTSTable
go
declare @.query varchar(200)
-- Insert MS_Word_document.doc into Row 5 !!
-- NOTE: Ensure the correct path for textcopy.exe!!
set @.query = 'D:\MSSQL80\MSSQL$SQL80\Binn\textcopy /s '+@.@.servername+' /u sa
/p<password> /d pubs /t FTSTable /c ImageCol /f
D:\SQLFiles\Shiloh\<MS_Word>.doc /i /k 5000 /w "where KeyCol=1"'
print @.query
exec master..xp_cmdshell @.query
go
-- Select data
SELECT * from FTSTable
go
-- FTI
use pubs
go
exec sp_fulltext_database 'enable' -- only do this once!
go
-- Drop FTI, if necessary...
-- exec sp_fulltext_table 'FTSTable','drop'
-- exec sp_fulltext_Catalog 'FTSCatalog','drop'
exec sp_fulltext_catalog 'FTSCatalog','create'
exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX'
exec sp_fulltext_column 'FTSTable','ImageCol','add', 0x0409, 'ExtCol'
exec sp_fulltext_column 'FTSTable','TextCol','add'
exec sp_fulltext_table 'FTSTable', 'activate'
go
-- Start FT Indexing...
exec sp_fulltext_catalog 'FTSCatalog','start_full'
go
-- Wait for FT Indexing to complete and check NT/Win2K Application log for
success/errors..
-- Search for search_word_here in MS_Word
select KeyCol, ImageCol from FTSTable where
contains(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Search for search_word_here in MS_Word file...
select KeyCol, ImageCol from FTSTable where
freetext(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Remove FT Indexes & Catalog & table..
exec sp_fulltext_table 'FTSTable','drop'
exec sp_fulltext_Catalog 'FTSCatalog','drop'
drop table FTSTable
Note, that to FT Search the Metadata info contained in the documents, such
as author, title, you wll need to programmaticlly extract this information
and store it in a textual column and then FT Index these columns in addition
to the document column.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!