We have this table...
CREATE TABLE [dbo].[Document](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[HumanResourceID] [int] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[ContentType] [nchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Content] [image] NOT NULL,
[DateEntered] [datetime] NOT NULL,
[DateModified] [datetime] NULL,
[Version] [timestamp] NOT NULL,
[EmployeeID] [int] NULL,
CONSTRAINT [Resume_PK] PRIMARY KEY CLUSTERED
(
[ResumeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Content contains the bits that make up either Word or RTF documents.
We have FTI defined on Content / ContentType / DocumentID. Generally FT
searches are working.
The table contains over 130k documents.
Through our application we are limiting their searches to the top 1500 rank
of any FT search. (So as not to over-burden our server.)
This works when the want to search the table for documents within the entire
company.
But what they would really like is the top 1500 rank for documents within
their office.
Is there a way to partition the the table by an OfficeID with some what to
pre-filter so the FT search is only looking at Documents from one or more
OfficeIDs?
TIA - Kyle!
I think I found my answer...although a lot of work.
Remove the FTI from the table.
Add an OfficeID column to the table and populate it.
Partition the table by the OfficeID column.
Create an Indexed View for each OfficeID
(Open a new office, the add a new OfficeID and a new Indexed View for that
OfficeID.)
(Close an existing Office, migrate the documents to a different office, drop
the FTI for that View and drop that View)
Add a FTI to each of the Indexed Views.
Mod the application so it knows what how to FT search one or more Indexed
Views and combine the results from multiple views if needed.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:OitVbpuHHHA.1468@.TK2MSFTNGP04.phx.gbl...
> We have this table...
> CREATE TABLE [dbo].[Document](
> [DocumentID] [int] IDENTITY(1,1) NOT NULL,
> [HumanResourceID] [int] NOT NULL,
> [Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [ContentType] [nchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [Content] [image] NOT NULL,
> [DateEntered] [datetime] NOT NULL,
> [DateModified] [datetime] NULL,
> [Version] [timestamp] NOT NULL,
> [EmployeeID] [int] NULL,
> CONSTRAINT [Resume_PK] PRIMARY KEY CLUSTERED
> (
> [ResumeID] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> Content contains the bits that make up either Word or RTF documents.
> We have FTI defined on Content / ContentType / DocumentID. Generally FT
> searches are working.
> The table contains over 130k documents.
> Through our application we are limiting their searches to the top 1500
> rank of any FT search. (So as not to over-burden our server.)
> This works when the want to search the table for documents within the
> entire company.
> But what they would really like is the top 1500 rank for documents within
> their office.
> Is there a way to partition the the table by an OfficeID with some what to
> pre-filter so the FT search is only looking at Documents from one or more
> OfficeIDs?
> TIA - Kyle!
>
|||Hello Kyle,
The other option is to add the office ID to the content (If the content was
editable i.e. text/html)
Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
SERVER DBA"')
If the content is editabel this is by far the more manageable, and scalable.
We did the index view thing and it is just not a neat solution. The token
thing is much easier.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> I think I found my answer...although a lot of work.
> Remove the FTI from the table.
> Add an OfficeID column to the table and populate it.
> Partition the table by the OfficeID column.
> Create an Indexed View for each OfficeID
> (Open a new office, the add a new OfficeID and a new Indexed View for
> that
> OfficeID.)
> (Close an existing Office, migrate the documents to a different
> office, drop
> the FTI for that View and drop that View)
> Add a FTI to each of the Indexed Views.
> Mod the application so it knows what how to FT search one or more
> Indexed Views and combine the results from multiple views if needed.
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in
> message news:OitVbpuHHHA.1468@.TK2MSFTNGP04.phx.gbl...
|||Um, maybe I'm missing something here, but since you are using the rank
I'm assuming you are using CONTAINSTABLE or FREETEXTTABLE and joining
back to Document. I'm also assuming you are using a "TOP 1500" and an
"ORDER BY Rank" in your query. Could you then just add "AND OfficeID =
'USEROFFICEID'" to your WHERE clause after adding the OfficeID field to
the table?
On Dec 14, 7:32 pm, Simon Sabin <SimonSa...@.noemail.noemail> wrote:[vbcol=seagreen]
> Hello Kyle,
> The other option is to add the office ID to the content (If the content was
> editable i.e. text/html)
> Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
> SERVER DBA"')
> If the content is editabel this is by far the more manageable, and scalable.
> We did the index view thing and it is just not a neat solution. The token
> thing is much easier.
> Simon Sabin
> SQL Server MVPhttp://sqlblogcasts.com/blogs/simons
>
>
>
>
>
>
>
>
>
|||Looks like I'm stuck.
In order to create a Full Text Index on my view, the view has to have a
unique index.
SQL 2005 doesn't allow you to create a index on a view if the view contains
an text, ntext, image or xml columns.
And mine does because we're storint the resume in an image column and the
resume is what we're after.
We can't add any special tokens, the content is an image field.
Any other ideas?
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a36ede8c8edf7abdbb584@.msnews.microsoft .com...
> Hello Kyle,
> The other option is to add the office ID to the content (If the content
> was editable i.e. text/html)
> Then use a query like containstable(document, content,'OFFICE2345 AND "SQL
> SERVER DBA"')
> If the content is editabel this is by far the more manageable, and
> scalable.
> We did the index view thing and it is just not a neat solution. The token
> thing is much easier.
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>
|||Kyle,
a) you can create an index on a view if you use VARCHAR(MAX) or
VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql 2005).
b) AFAIK, those "tokens" can be other columns from the same view/table that
are also indexed in that FT catalog. You just specify CONTAINS(*,... Instead
of CONTAINS(MyClobColumn,...
it should work.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
> Looks like I'm stuck.
> In order to create a Full Text Index on my view, the view has to have a
> unique index.
> SQL 2005 doesn't allow you to create a index on a view if the view
> contains an text, ntext, image or xml columns.
> And mine does because we're storint the resume in an image column and the
> resume is what we're after.
> We can't add any special tokens, the content is an image field.
> Any other ideas?
>
> "Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
> news:62959f1a36ede8c8edf7abdbb584@.msnews.microsoft .com...
>
|||I will have to try changing the type over to VARBINARY(MAX). That may work.
It mght also be easier to FTI a second column since that will require less
program changes.
Thanks
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||I added Office nvchar(8) to my table and populated int
select count(DocumentID) from Document where contains(*, 'Office01 and
cobol')
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||I added a Office nvchar(8) column and populated it.
No matter what I specify for a search condition, it doesn't return what I'm
after.
For a test I tried
select count(DocumentID) from Document where contains(*, 'Office02 and
cobol')
'Office02' is in the new column, 'cobol' is in the image column.
If I search for them seperately there is overlap so the data is correct.
Ideas?
"Lakusha" <Lakusha@.excite.com> wrote in message
news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Kyle,
> a) you can create an index on a view if you use VARCHAR(MAX) or
> VARBINARY(MAX) instead of TEXT or IMAGE (which are deprecated in sql
> 2005).
> b) AFAIK, those "tokens" can be other columns from the same view/table
> that are also indexed in that FT catalog. You just specify CONTAINS(*,...
> Instead of CONTAINS(MyClobColumn,...
> it should work.
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:%23vIoXwFJHHA.536@.TK2MSFTNGP02.phx.gbl...
>
|||To be clear the 'Office' column will only ever hold an office identifier
liket 'Office01' or 'Office13'.
The Content column has the interedting data.
We want to use containstable to give us the top n by rank DocumentIDs for a
particular office.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uM6mqsRKHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I added a Office nvchar(8) column and populated it.
> No matter what I specify for a search condition, it doesn't return what
> I'm after.
> For a test I tried
> select count(DocumentID) from Document where contains(*, 'Office02 and
> cobol')
> 'Office02' is in the new column, 'cobol' is in the image column.
> If I search for them seperately there is overlap so the data is correct.
> Ideas?
> "Lakusha" <Lakusha@.excite.com> wrote in message
> news:uWBdE0KJHHA.3552@.TK2MSFTNGP03.phx.gbl...
>
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Monday, March 26, 2012
FTI and multiple columns - another question, but opposite behaviour?
I have a table with 2 FTI columns, but my query results seems strange. If I
run this query:
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
it returns no rows.
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
this returns one row as both words are in the same FTI column.
AND CONTAINS( J.*, '"FIFE"' )
returns the same row, as FIFE exists in the other FTI column.
So, why doesn't the top query return the row in the results? Is this normal
behaviour? Is there anything I can do to make it work?
Thanks.
A contains query can't look across columns, a freetext query can. So if your
hits come from different columns a freetext query will return a hit, whereas
a contains won't.
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
"Joe Bloggs" <Joe.Bloggs@.acme.com> wrote in message
news:%23C6lxLZcFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I have a table with 2 FTI columns, but my query results seems strange. If
I
> run this query:
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
> it returns no rows.
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
> this returns one row as both words are in the same FTI column.
> AND CONTAINS( J.*, '"FIFE"' )
> returns the same row, as FIFE exists in the other FTI column.
> So, why doesn't the top query return the row in the results? Is this
normal
> behaviour? Is there anything I can do to make it work?
> Thanks.
>
run this query:
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
it returns no rows.
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
this returns one row as both words are in the same FTI column.
AND CONTAINS( J.*, '"FIFE"' )
returns the same row, as FIFE exists in the other FTI column.
So, why doesn't the top query return the row in the results? Is this normal
behaviour? Is there anything I can do to make it work?
Thanks.
A contains query can't look across columns, a freetext query can. So if your
hits come from different columns a freetext query will return a hit, whereas
a contains won't.
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
"Joe Bloggs" <Joe.Bloggs@.acme.com> wrote in message
news:%23C6lxLZcFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I have a table with 2 FTI columns, but my query results seems strange. If
I
> run this query:
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
> it returns no rows.
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
> this returns one row as both words are in the same FTI column.
> AND CONTAINS( J.*, '"FIFE"' )
> returns the same row, as FIFE exists in the other FTI column.
> So, why doesn't the top query return the row in the results? Is this
normal
> behaviour? Is there anything I can do to make it work?
> Thanks.
>
FTE locks the table
I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?
Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
sql
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?
Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
sql
FTE locks the table
I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
FTE locks the table
I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:206A8F61-21C9-475B-9095-C6708D29A09C@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
ft search error in win2k3 sp1 with sql 2k sp4?
I just setup my server with win2k3 sp1 and ALL the latest patches, plus
sql 2004 standard sp4.
I keep getting this error on a table with 39000 records where I am
running a basic wildcard CONTAINS fulltext query for "cer*"
Server: Msg 7643, Level 16, State 1, Line 1
Your search generated too many results. Please perform a more specific
search.
There should be about 30000 records.
I noticed on technet that this was a problem pre sp2 in sql, but was
since patched. Has the bug recently been re-introduced in some combo
of win2k3 and sql patches?
Phadke,
Yes, there was some additional logic added in SP3 that generates the below
error message. SQL FTS is optimized for returning 2000 results per query,
primarily for performance reasons. You may want to re-write your query using
CONTAINSTABLE and use the Top_N_by_Rank parameter to limit the results to
2000 or use a similar TOP restriction in the select list.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129632193.545575.218860@.g47g2000cwa.googlegr oups.com...
>I just setup my server with win2k3 sp1 and ALL the latest patches, plus
> sql 2004 standard sp4.
> I keep getting this error on a table with 39000 records where I am
> running a basic wildcard CONTAINS fulltext query for "cer*"
> Server: Msg 7643, Level 16, State 1, Line 1
> Your search generated too many results. Please perform a more specific
> search.
> There should be about 30000 records.
> I noticed on technet that this was a problem pre sp2 in sql, but was
> since patched. Has the bug recently been re-introduced in some combo
> of win2k3 and sql patches?
>
|||John,
Just to clarify, is this a hard limit of 2000, because I am able to see
the result set for queries that return a con query with 17-18k records
with no problem.
I saw another technote that the problem limit was at approx 20k
records:
http://support.microsoft.com/default...;en-us;Q296456
Thanks for the info,
Aditya
|||Also, is this limitation common to sql 2005?
|||Ok, now I am really confused...I ran a fulltext query for "c*" on a
database on another server. This database has 2.4 Million Records.
This server is Win2k3 (no SP) and SQL 2k SP3.
When I run the query, I get 900,000 records back without an error
message.
|||Aditya,
Yes, this is the KB article that I couldn't find early this morning when in
a rush to get off to work...
Still for performance reasons, it might be best to use Top_N_Rank with
CONTAINSTABLE or is there a specific reason you want to return this many
rows?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129660216.247521.205300@.g47g2000cwa.googlegr oups.com...
> John,
> Just to clarify, is this a hard limit of 2000, because I am able to see
> the result set for queries that return a con query with 17-18k records
> with no problem.
> I saw another technote that the problem limit was at approx 20k
> records:
> http://support.microsoft.com/default...;en-us;Q296456
> Thanks for the info,
> Aditya
>
|||Aditya,
I need to confirm this with a large table in order to generate the large
results. However, I would suspect that it is still in SQL 2005 until proved
otherwise...
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129660966.529955.176010@.g44g2000cwa.googlegr oups.com...
> Also, is this limitation common to sql 2005?
>
|||Aditya,
Different servers can behavior differently for FTS results based upon the OS
platform (different wordbreakers) and due to different noise word file
contents. In this case, and on this server is the single letter "C" removed
from the language-specific noise word file (noise.enu = US English) for the
specific FT-enable column's "Language for Word Breaker"?
As they say the devil is in the details... ;-)
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129672239.794380.257680@.o13g2000cwo.googlegr oups.com...
> Ok, now I am really confused...I ran a fulltext query for "c*" on a
> database on another server. This database has 2.4 Million Records.
> This server is Win2k3 (no SP) and SQL 2k SP3.
> When I run the query, I get 900,000 records back without an error
> message.
>
sql 2004 standard sp4.
I keep getting this error on a table with 39000 records where I am
running a basic wildcard CONTAINS fulltext query for "cer*"
Server: Msg 7643, Level 16, State 1, Line 1
Your search generated too many results. Please perform a more specific
search.
There should be about 30000 records.
I noticed on technet that this was a problem pre sp2 in sql, but was
since patched. Has the bug recently been re-introduced in some combo
of win2k3 and sql patches?
Phadke,
Yes, there was some additional logic added in SP3 that generates the below
error message. SQL FTS is optimized for returning 2000 results per query,
primarily for performance reasons. You may want to re-write your query using
CONTAINSTABLE and use the Top_N_by_Rank parameter to limit the results to
2000 or use a similar TOP restriction in the select list.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129632193.545575.218860@.g47g2000cwa.googlegr oups.com...
>I just setup my server with win2k3 sp1 and ALL the latest patches, plus
> sql 2004 standard sp4.
> I keep getting this error on a table with 39000 records where I am
> running a basic wildcard CONTAINS fulltext query for "cer*"
> Server: Msg 7643, Level 16, State 1, Line 1
> Your search generated too many results. Please perform a more specific
> search.
> There should be about 30000 records.
> I noticed on technet that this was a problem pre sp2 in sql, but was
> since patched. Has the bug recently been re-introduced in some combo
> of win2k3 and sql patches?
>
|||John,
Just to clarify, is this a hard limit of 2000, because I am able to see
the result set for queries that return a con query with 17-18k records
with no problem.
I saw another technote that the problem limit was at approx 20k
records:
http://support.microsoft.com/default...;en-us;Q296456
Thanks for the info,
Aditya
|||Also, is this limitation common to sql 2005?
|||Ok, now I am really confused...I ran a fulltext query for "c*" on a
database on another server. This database has 2.4 Million Records.
This server is Win2k3 (no SP) and SQL 2k SP3.
When I run the query, I get 900,000 records back without an error
message.
|||Aditya,
Yes, this is the KB article that I couldn't find early this morning when in
a rush to get off to work...
Still for performance reasons, it might be best to use Top_N_Rank with
CONTAINSTABLE or is there a specific reason you want to return this many
rows?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129660216.247521.205300@.g47g2000cwa.googlegr oups.com...
> John,
> Just to clarify, is this a hard limit of 2000, because I am able to see
> the result set for queries that return a con query with 17-18k records
> with no problem.
> I saw another technote that the problem limit was at approx 20k
> records:
> http://support.microsoft.com/default...;en-us;Q296456
> Thanks for the info,
> Aditya
>
|||Aditya,
I need to confirm this with a large table in order to generate the large
results. However, I would suspect that it is still in SQL 2005 until proved
otherwise...
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129660966.529955.176010@.g44g2000cwa.googlegr oups.com...
> Also, is this limitation common to sql 2005?
>
|||Aditya,
Different servers can behavior differently for FTS results based upon the OS
platform (different wordbreakers) and due to different noise word file
contents. In this case, and on this server is the single letter "C" removed
from the language-specific noise word file (noise.enu = US English) for the
specific FT-enable column's "Language for Word Breaker"?
As they say the devil is in the details... ;-)
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129672239.794380.257680@.o13g2000cwo.googlegr oups.com...
> Ok, now I am really confused...I ran a fulltext query for "c*" on a
> database on another server. This database has 2.4 Million Records.
> This server is Win2k3 (no SP) and SQL 2k SP3.
> When I run the query, I get 900,000 records back without an error
> message.
>
FT Locks the table!
I have a table with Full Text index on it, the table has at the most 300
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?
Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:60169C43-9233-4021-88C2-9C200A4E0462@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
|||Thank you Hilary.
I checked and i have the change tracking set to Auto.
I cannot find where i can change the resource usage for FT.
Can you help please?
It is Sql 2005.
"Hilary Cotter" wrote:
> Full-text indexing will cause some locking as it needs to create some level
> of consistency with what is in the catalogs and what is in the database. Are
> you using change tracking? If not you really should. You also might want to
> set your resource usage to something smaller.
> One more point, it is possible that other non-optimal procs or ad hoq sql
> statements are themselves not tuned and are causing overall performance
> problems manifesting itself in FTI locking.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
> news:60169C43-9233-4021-88C2-9C200A4E0462@.microsoft.com...
>
>
|||sp_fulltext_service 'resource usage',2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:0953B0F8-5E75-482A-A273-DFA94BEAEA73@.microsoft.com...[vbcol=seagreen]
> Thank you Hilary.
> I checked and i have the change tracking set to Auto.
> I cannot find where i can change the resource usage for FT.
> Can you help please?
> It is Sql 2005.
>
> "Hilary Cotter" wrote:
records.
The database is on the live server and gets hit a lot.
Every morning when i come to work the table is inaccessible, meaning any
query that i issue to this table runs indefinitely, Select, update, Insert
all just hang.
The only way for me to fix this is manually End The Full Text Sql Windows
Task, in task manager. As soon as i end the task it restarts immediately and
everything is working fine after.
Is there a patch for Sql 2005 to fix this, did anyone experience something
like that?
Full-text indexing will cause some locking as it needs to create some level
of consistency with what is in the catalogs and what is in the database. Are
you using change tracking? If not you really should. You also might want to
set your resource usage to something smaller.
One more point, it is possible that other non-optimal procs or ad hoq sql
statements are themselves not tuned and are causing overall performance
problems manifesting itself in FTI locking.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:60169C43-9233-4021-88C2-9C200A4E0462@.microsoft.com...
>I have a table with Full Text index on it, the table has at the most 300
> records.
> The database is on the live server and gets hit a lot.
> Every morning when i come to work the table is inaccessible, meaning any
> query that i issue to this table runs indefinitely, Select, update, Insert
> all just hang.
> The only way for me to fix this is manually End The Full Text Sql Windows
> Task, in task manager. As soon as i end the task it restarts immediately
> and
> everything is working fine after.
> Is there a patch for Sql 2005 to fix this, did anyone experience something
> like that?
>
|||Thank you Hilary.
I checked and i have the change tracking set to Auto.
I cannot find where i can change the resource usage for FT.
Can you help please?
It is Sql 2005.
"Hilary Cotter" wrote:
> Full-text indexing will cause some locking as it needs to create some level
> of consistency with what is in the catalogs and what is in the database. Are
> you using change tracking? If not you really should. You also might want to
> set your resource usage to something smaller.
> One more point, it is possible that other non-optimal procs or ad hoq sql
> statements are themselves not tuned and are causing overall performance
> problems manifesting itself in FTI locking.
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
> news:60169C43-9233-4021-88C2-9C200A4E0462@.microsoft.com...
>
>
|||sp_fulltext_service 'resource usage',2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike Kansky" <MikeKansky@.discussions.microsoft.com> wrote in message
news:0953B0F8-5E75-482A-A273-DFA94BEAEA73@.microsoft.com...[vbcol=seagreen]
> Thank you Hilary.
> I checked and i have the change tracking set to Auto.
> I cannot find where i can change the resource usage for FT.
> Can you help please?
> It is Sql 2005.
>
> "Hilary Cotter" wrote:
FT Index on column with multiple language doesn't work - any ideas
Hi all,
I have a table on SQL Server 2005 with a column (collation
SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
the possible languages include English, German, French, Kazakh, Arabic,
Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
I tried querying for documents that contains 台灣 (meaning "Taiwan" in
Simplified Chinese), but it is not working as I expected.
I ran the following query:
select * from tblDocuments
where CONTAINS(DocumentTitle, '台灣', LANGUAGE 'Traditional Chinese')
With the English language breaker, I get 0 results.
With the Traditional Chinese language breaker, I get 1 result and it is the
document I was looking for.
I would've expected the LANGUAGE keyword to ignore the default language
breaker and use whatever is specified, but apparently I'm missing
something... maybe table setup or whatnot.
Any ideas would be greatly appreaciated.
Cheers,
Andry
This is correct behavior. When the document is indexed and you are
specifying the English word breaker it interprets the contents of the row to
be English words, unless you are using the xml language tags and specify
that the XML doc is in Chinese and that you are using the XML data type, or
you are using Word and marked that passage containing the character as
Chinese, or the language settings for your Word document are Chinese, or you
are using html and have set the ms.locale to Chinese and set the correct
code page. With Word Docs and HTML docs you must save them in the image or
varbinary data type columns and use a document type column with the
extension the document would have if it was stored in the file system.
If you don't follow the above the Chinese character will be interpreted as a
Unicode sequence which will not be interpreted correctly in your queries.
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
"Andry" <Andry@.discussions.microsoft.com> wrote in message
news:569E9782-47E3-4657-AC68-7787214D3D97@.microsoft.com...
> Hi all,
> I have a table on SQL Server 2005 with a column (collation
> SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
> the possible languages include English, German, French, Kazakh, Arabic,
> Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
> I tried querying for documents that contains ? (meaning "Taiwan" in
> Simplified Chinese), but it is not working as I expected.
> I ran the following query:
> select * from tblDocuments
> where CONTAINS(DocumentTitle, '?', LANGUAGE 'Traditional Chinese')
> With the English language breaker, I get 0 results.
> With the Traditional Chinese language breaker, I get 1 result and it is
> the
> document I was looking for.
> I would've expected the LANGUAGE keyword to ignore the default language
> breaker and use whatever is specified, but apparently I'm missing
> something... maybe table setup or whatnot.
> Any ideas would be greatly appreaciated.
> Cheers,
> Andry
I have a table on SQL Server 2005 with a column (collation
SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
the possible languages include English, German, French, Kazakh, Arabic,
Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
I tried querying for documents that contains 台灣 (meaning "Taiwan" in
Simplified Chinese), but it is not working as I expected.
I ran the following query:
select * from tblDocuments
where CONTAINS(DocumentTitle, '台灣', LANGUAGE 'Traditional Chinese')
With the English language breaker, I get 0 results.
With the Traditional Chinese language breaker, I get 1 result and it is the
document I was looking for.
I would've expected the LANGUAGE keyword to ignore the default language
breaker and use whatever is specified, but apparently I'm missing
something... maybe table setup or whatnot.
Any ideas would be greatly appreaciated.
Cheers,
Andry
This is correct behavior. When the document is indexed and you are
specifying the English word breaker it interprets the contents of the row to
be English words, unless you are using the xml language tags and specify
that the XML doc is in Chinese and that you are using the XML data type, or
you are using Word and marked that passage containing the character as
Chinese, or the language settings for your Word document are Chinese, or you
are using html and have set the ms.locale to Chinese and set the correct
code page. With Word Docs and HTML docs you must save them in the image or
varbinary data type columns and use a document type column with the
extension the document would have if it was stored in the file system.
If you don't follow the above the Chinese character will be interpreted as a
Unicode sequence which will not be interpreted correctly in your queries.
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
"Andry" <Andry@.discussions.microsoft.com> wrote in message
news:569E9782-47E3-4657-AC68-7787214D3D97@.microsoft.com...
> Hi all,
> I have a table on SQL Server 2005 with a column (collation
> SQL_Latin1_General_CP1_CI_AS) that can contain multiple languages. Some of
> the possible languages include English, German, French, Kazakh, Arabic,
> Chinese (Simplified and Traditional), Japanese, Korean, Thai, etc.
> I tried querying for documents that contains ? (meaning "Taiwan" in
> Simplified Chinese), but it is not working as I expected.
> I ran the following query:
> select * from tblDocuments
> where CONTAINS(DocumentTitle, '?', LANGUAGE 'Traditional Chinese')
> With the English language breaker, I get 0 results.
> With the Traditional Chinese language breaker, I get 1 result and it is
> the
> document I was looking for.
> I would've expected the LANGUAGE keyword to ignore the default language
> breaker and use whatever is specified, but apparently I'm missing
> something... maybe table setup or whatnot.
> Any ideas would be greatly appreaciated.
> Cheers,
> Andry
FT index not function ?
I configure a table with FT index , but when i search the
table , nothing returned , why ?
Windows 2003 EE (Chinese Edition)
SQL Server 2k + SP3 (Chinese Edition)
1. makefile C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
2. start the MSSearch service with local system account.
3. connect to SQL Database with isqlw
create table docbase , the type of dcont field is image
and to store my document of word,excel,ppt,htm etc.
4. enable Fulltext of database
sp_fulltext_database 'enable'
5. create catalogs
execute
sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
execute
sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
'
execute sp_fulltext_column 'DOCBASE' , 'DCONT' , 'add' ,
0x0804 , 'DOCTYPE'
-- language is Simplified Chinese 0x0804
execute sp_fulltext_table 'DOCBASE' , 'activate'
execute sp_fulltext_catalog 'ft_test','start_full'
EXEC sp_tableoption 'DOCBASE', 'text in row', 'ON'
EXEC sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
EXEC
sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
6. put some docs of type .doc or .xls or .ppt into table
docbase by ASP.net applications
7. select kbid , doctype from docbase where freetext (
dcont , 'ChineseChars')
nothing returned. But i'm sure the content of the docs
include the 'ChineseChars' !
any adea ? thanks !
can you issue this query for me in the database you are Full Text indexing?
select FulltextCatalogProperty(N'test', N'ItemCount')
Also can you look for any messages in your event log from Microsoft Search
with an event ID of 3018 and post the text of this message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:02be01c4b804$61ab6920$a501280a@.phx.gbl...
> I configure a table with FT index , but when i search the
> table , nothing returned , why ?
> Windows 2003 EE (Chinese Edition)
> SQL Server 2k + SP3 (Chinese Edition)
> 1. makefile C:\Program Files\Microsoft SQL
> Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
> 2. start the MSSearch service with local system account.
> 3. connect to SQL Database with isqlw
> create table docbase , the type of dcont field is image
> and to store my document of word,excel,ppt,htm etc.
> 4. enable Fulltext of database
> sp_fulltext_database 'enable'
> 5. create catalogs
> execute
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> execute
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> '
> execute sp_fulltext_column 'DOCBASE' , 'DCONT' , 'add' ,
> 0x0804 , 'DOCTYPE'
> -- language is Simplified Chinese 0x0804
> execute sp_fulltext_table 'DOCBASE' , 'activate'
> execute sp_fulltext_catalog 'ft_test','start_full'
> EXEC sp_tableoption 'DOCBASE', 'text in row', 'ON'
> EXEC sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> EXEC
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
> 6. put some docs of type .doc or .xls or .ppt into table
> docbase by ASP.net applications
> 7. select kbid , doctype from docbase where freetext (
> dcont , 'ChineseChars')
> nothing returned. But i'm sure the content of the docs
> include the 'ChineseChars' !
> any adea ? thanks !
>
|||Thanks , Cotter
1. > select FulltextCatalogProperty(N'ft_test',
N'ItemCount')
> 2
2. the 3018 message is:
The end of crawl for project <SQLServer SQL0000500005> has
been detected. The
Gatherer successfully processed 6 documents totaling 0K.
It failed to filter 0
documents. URLs could not be reached or were denied access.
3. What's the filter , How to pluggin the filter of
document (word ,excel , pdf etc )into the MS Search
Service ?
>--Original Message--
>can you issue this query for me in the database you are
Full Text indexing?
>select FulltextCatalogProperty(N'test', N'ItemCount')
>Also can you look for any messages in your event log from
Microsoft Search
>with an event ID of 3018 and post the text of this
message here?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rainbow" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:02be01c4b804$61ab6920$a501280a@.phx.gbl...
the[vbcol=seagreen]
sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'[vbcol=seagreen]
sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE[vbcol=seagreen]
sp_fulltext_table 'DOCBASE', 'Start_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
>
>.
>
|||rainbow,
From the below results, it does appear that at least 5 rows were
successfully FT Indexed. Did you find any other related "Microsoft Search"
or MssCi source event errors? Can I assume that the files () that were
inserted in by your ASP.NET application contained only Chinese characters
and that you issued SQL FTS queries using Chinese characters? If so, and if
the documents also contain English words, could you search on the English
words? There is a know bug ( bug# 356712) with SQL Server 2000, that
"Full-Text: sp_fulltext_column allows you to specify a language on an IMAGE
column, though it has no effect on indexing... DOC" and if you are
searching on the Chinese characters that the FTS query will fail to return
results.
Thanks,
John
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:115701c4ba2c$81213120$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks , Cotter
> 1. > select FulltextCatalogProperty(N'ft_test',
> N'ItemCount')
> 2. the 3018 message is:
> The end of crawl for project <SQLServer SQL0000500005> has
> been detected. The
> Gatherer successfully processed 6 documents totaling 0K.
> It failed to filter 0
> documents. URLs could not be reached or were denied access.
> 3. What's the filter , How to pluggin the filter of
> document (word ,excel , pdf etc )into the MS Search
> Service ?
>
> Full Text indexing?
> Microsoft Search
> message here?
> message
> the
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
|||1) ok, it looks like you have indexed at least one row, or perhaps you are
indexing two tables.
2) Is this a recent message for the pubs database?
3) the iFilter for office docs ships with win203 as part of the base
operating system so we know its there.
Can you try to index the authors table and see if you can query this. This
will help to verify that indexing is working currently. I suspect the 3018
is not a recent message. If it is a recent message indexing appears to be
working and your problem could be related to language resources.
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:115701c4ba2c$81213120$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks , Cotter
> 1. > select FulltextCatalogProperty(N'ft_test',
> N'ItemCount')
> 2. the 3018 message is:
> The end of crawl for project <SQLServer SQL0000500005> has
> been detected. The
> Gatherer successfully processed 6 documents totaling 0K.
> It failed to filter 0
> documents. URLs could not be reached or were denied access.
> 3. What's the filter , How to pluggin the filter of
> document (word ,excel , pdf etc )into the MS Search
> Service ?
>
> Full Text indexing?
> Microsoft Search
> message here?
> message
> the
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
sql
table , nothing returned , why ?
Windows 2003 EE (Chinese Edition)
SQL Server 2k + SP3 (Chinese Edition)
1. makefile C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
2. start the MSSearch service with local system account.
3. connect to SQL Database with isqlw
create table docbase , the type of dcont field is image
and to store my document of word,excel,ppt,htm etc.
4. enable Fulltext of database
sp_fulltext_database 'enable'
5. create catalogs
execute
sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
execute
sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
'
execute sp_fulltext_column 'DOCBASE' , 'DCONT' , 'add' ,
0x0804 , 'DOCTYPE'
-- language is Simplified Chinese 0x0804
execute sp_fulltext_table 'DOCBASE' , 'activate'
execute sp_fulltext_catalog 'ft_test','start_full'
EXEC sp_tableoption 'DOCBASE', 'text in row', 'ON'
EXEC sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
EXEC
sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
6. put some docs of type .doc or .xls or .ppt into table
docbase by ASP.net applications
7. select kbid , doctype from docbase where freetext (
dcont , 'ChineseChars')
nothing returned. But i'm sure the content of the docs
include the 'ChineseChars' !
any adea ? thanks !
can you issue this query for me in the database you are Full Text indexing?
select FulltextCatalogProperty(N'test', N'ItemCount')
Also can you look for any messages in your event log from Microsoft Search
with an event ID of 3018 and post the text of this message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:02be01c4b804$61ab6920$a501280a@.phx.gbl...
> I configure a table with FT index , but when i search the
> table , nothing returned , why ?
> Windows 2003 EE (Chinese Edition)
> SQL Server 2k + SP3 (Chinese Edition)
> 1. makefile C:\Program Files\Microsoft SQL
> Server\MSSQL\FTDATA\SQLServer\Config\noise.chs
> 2. start the MSSearch service with local system account.
> 3. connect to SQL Database with isqlw
> create table docbase , the type of dcont field is image
> and to store my document of word,excel,ppt,htm etc.
> 4. enable Fulltext of database
> sp_fulltext_database 'enable'
> 5. create catalogs
> execute
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> execute
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> '
> execute sp_fulltext_column 'DOCBASE' , 'DCONT' , 'add' ,
> 0x0804 , 'DOCTYPE'
> -- language is Simplified Chinese 0x0804
> execute sp_fulltext_table 'DOCBASE' , 'activate'
> execute sp_fulltext_catalog 'ft_test','start_full'
> EXEC sp_tableoption 'DOCBASE', 'text in row', 'ON'
> EXEC sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> EXEC
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
> 6. put some docs of type .doc or .xls or .ppt into table
> docbase by ASP.net applications
> 7. select kbid , doctype from docbase where freetext (
> dcont , 'ChineseChars')
> nothing returned. But i'm sure the content of the docs
> include the 'ChineseChars' !
> any adea ? thanks !
>
|||Thanks , Cotter
1. > select FulltextCatalogProperty(N'ft_test',
N'ItemCount')
> 2
2. the 3018 message is:
The end of crawl for project <SQLServer SQL0000500005> has
been detected. The
Gatherer successfully processed 6 documents totaling 0K.
It failed to filter 0
documents. URLs could not be reached or were denied access.
3. What's the filter , How to pluggin the filter of
document (word ,excel , pdf etc )into the MS Search
Service ?
>--Original Message--
>can you issue this query for me in the database you are
Full Text indexing?
>select FulltextCatalogProperty(N'test', N'ItemCount')
>Also can you look for any messages in your event log from
Microsoft Search
>with an event ID of 3018 and post the text of this
message here?
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"rainbow" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:02be01c4b804$61ab6920$a501280a@.phx.gbl...
the[vbcol=seagreen]
sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'[vbcol=seagreen]
sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE[vbcol=seagreen]
sp_fulltext_table 'DOCBASE', 'Start_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
>
>.
>
|||rainbow,
From the below results, it does appear that at least 5 rows were
successfully FT Indexed. Did you find any other related "Microsoft Search"
or MssCi source event errors? Can I assume that the files () that were
inserted in by your ASP.NET application contained only Chinese characters
and that you issued SQL FTS queries using Chinese characters? If so, and if
the documents also contain English words, could you search on the English
words? There is a know bug ( bug# 356712) with SQL Server 2000, that
"Full-Text: sp_fulltext_column allows you to specify a language on an IMAGE
column, though it has no effect on indexing... DOC" and if you are
searching on the Chinese characters that the FTS query will fail to return
results.
Thanks,
John
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:115701c4ba2c$81213120$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks , Cotter
> 1. > select FulltextCatalogProperty(N'ft_test',
> N'ItemCount')
> 2. the 3018 message is:
> The end of crawl for project <SQLServer SQL0000500005> has
> been detected. The
> Gatherer successfully processed 6 documents totaling 0K.
> It failed to filter 0
> documents. URLs could not be reached or were denied access.
> 3. What's the filter , How to pluggin the filter of
> document (word ,excel , pdf etc )into the MS Search
> Service ?
>
> Full Text indexing?
> Microsoft Search
> message here?
> message
> the
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
|||1) ok, it looks like you have indexed at least one row, or perhaps you are
indexing two tables.
2) Is this a recent message for the pubs database?
3) the iFilter for office docs ships with win203 as part of the base
operating system so we know its there.
Can you try to index the authors table and see if you can query this. This
will help to verify that indexing is working currently. I suspect the 3018
is not a recent message. If it is a recent message indexing appears to be
working and your problem could be related to language resources.
"rainbow" <anonymous@.discussions.microsoft.com> wrote in message
news:115701c4ba2c$81213120$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks , Cotter
> 1. > select FulltextCatalogProperty(N'ft_test',
> N'ItemCount')
> 2. the 3018 message is:
> The end of crawl for project <SQLServer SQL0000500005> has
> been detected. The
> Gatherer successfully processed 6 documents totaling 0K.
> It failed to filter 0
> documents. URLs could not be reached or were denied access.
> 3. What's the filter , How to pluggin the filter of
> document (word ,excel , pdf etc )into the MS Search
> Service ?
>
> Full Text indexing?
> Microsoft Search
> message here?
> message
> the
> sp_fulltext_catalog 'ft_test','create','U:\minicent\FTDATA'
> sp_fulltext_table 'DOCBASE','create', 'ft_test','PK_DOCBASE
> sp_fulltext_table 'DOCBASE', 'Start_change_tracking'
> sp_fulltext_table 'DOCBASE', 'Start_background_updateindex'
sql
Friday, March 23, 2012
Frustrated
I'm not to familiar with SQL so I'm frustrated. The deal is this: I have a table with product information in it. I'm creating an xml doc from this data via a datagrid. I have to add two extra fields into the table to match my DTD. I did that fine and what I need to do is set those fields equal to the value in another. I need to pull my id field value and store that in the BuyUrl field which is appended to the end of a url. How can I achieve this? Thanks for your help.Select
field1, field2, field3,
field1 as field4, field2 as field5
from table1
field1, field2, field3,
field1 as field4, field2 as field5
from table1
You can select fields multiple times in a SELECT statement. Using the AS operator assigns a new name to the column (for purposes of the query).|||::I'm not to familiar with SQL so I'm frustrated,
In geneeral reading the documentation or a good book helps.
I would recommend SQL for dummies.
http://www.amazon.com/exec/obidos/tg/detail/-/0764540750/qid=1085204033/sr=8-1/ref=pd_ka_1/104-5531251-7639964?v=glance&s=books&n=507846
or
Sql Server 2000 for dummies
http://www.amazon.com/exec/obidos/tg/detail/-/0764507753/qid=1085204058/sr=8-2/ref=pd_ka_2/104-5531251-7639964?v=glance&s=books&n=507846
If you really have a generic problems with your knowledge about SQL, then any particular help to answer one qusstion is wasted help. Among the lines of:
Give a hungy man a fish, and he will not be hungry for the day. Teach him how to fish, and he will never be hungry again.
I suggest sitting down and getting one o fthe books and reading them.|||lots of people rec' "SQL for Dummies", might be worth a look. No offense.|||Nevermind I figured it out without the book. Seems like developers are turning out to be like network engineers. No one wants to help anyone any more. When you need a quick answer because you're trying to meet a deadline a quick answer or a point in the right direction would be appreciated. Not go buy the book. Thanks.|||Sorry you feel like that. When the question is so very simple its more helpful to direct you to somewhere you can learn about the problem and other simple issues that you're bound to fall foul of.
Wednesday, March 21, 2012
Front Page 2002 and SQL Query
I am using a very basic query in Front Page 2002 against a SQL table in
SQL 2000
SQL 2000
SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)
and f_docnumber is a search form field.
When I run I get
Description: The precision is invalid.
Number: -2147467259 (0x80004005)
The field is defined in the table as numberic 9(10,0)
Any ideas?(webforcam@.hotmail.com) writes:
> I am using a very basic query in Front Page 2002 against a SQL table in
> SQL 2000
> SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)
> and f_docnumber is a search form field.
> When I run I get
> Description: The precision is invalid.
> Number: -2147467259 (0x80004005)
> The field is defined in the table as numberic 9(10,0)
I have used FrontPage to author the articles on my web site, but I
have ot idea how you query databases from the tool. So maybe you ask
this question in a FrontPage newsgroup. That much I can say is that
the error does not come SQL Server, but from the OLE DB provider.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
front end options for entering data into an oracle database
Hi,
I need to set up a generic table in Oracle that allows users to enter
data that can later be retrieved in reports.
I was thinking of a 3 column table in this structure:
DATE,ITEM,VALUE
figuring that you can store anything in this way because ITEM can be
anything and you can have multiple instances of a particular value by
having a new date.
Does that seem reasonable?
In any case, what are my user front end options? I was thinking a
Microsoft Access database but its not ideal because not everyone here
has Access?
Can you do it with Excel? Or what other options are there as a simple
user front end to enter the data?
Thanks for any advice.
Kimanasttin@.excite.com wrote:
> Hi,
> I need to set up a generic table in Oracle that allows users to enter
> data that can later be retrieved in reports.
> I was thinking of a 3 column table in this structure:
> DATE,ITEM,VALUE
> figuring that you can store anything in this way because ITEM can be
> anything and you can have multiple instances of a particular value by
> having a new date.
> Does that seem reasonable?
No. It's a classic design error that defeats most of the reasons why we
use a DBMS at all.
http://tonyandrews.blogspot.com/200...n-mistakes.html
Also, this is a Microsoft SQL Server group. You'll most probably get
more help in an Oracle-specific group.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--
from XML file to sqlserver table
Hello; I'm a newby with openxml; I'm sorry in advance for my question. I've
found a lot of documentation about openxml but I've still a doubt...
In this simple example I'm able to load in a table an extract of a xml file:
here you are:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a INSERT statement that uses the OPENXML rowset provider.
INSERT INTO CUSTOMER
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
The question is very simple, probably; how can I manage the file without
"embed it" in to the code between the ' ' (set @.doc='...')?
In fact my file is potentially large and I would like to open the file and
insert the record dinamically without paste it in the procedure code....
Thanks in advance.
Bruno Stefanutti
The best way is to put the expression into a stored procedure and pass the
value as a paramater (you can use TEXT or NTEXT as the parameter type).
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
> Hello; I'm a newby with openxml; I'm sorry in advance for my question.
> I've
> found a lot of documentation about openxml but I've still a doubt...
> In this simple example I'm able to load in a table an extract of a xml
> file:
> here you are:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order CustomerID="VINET" EmployeeID="5"
> OrderDate="1996-07-04T00:00:00">
> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order CustomerID="LILAS" EmployeeID="3"
> OrderDate="1996-08-16T00:00:00">
> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a INSERT statement that uses the OPENXML rowset provider.
> INSERT INTO CUSTOMER
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> The question is very simple, probably; how can I manage the file without
> "embed it" in to the code between the ' ' (set @.doc='...')?
> In fact my file is potentially large and I would like to open the file
> and
> insert the record dinamically without paste it in the procedure code....
> Thanks in advance.
> Bruno Stefanutti
>
>
|||...When you say "expression" this means I have to pass the path of my xml
file as a parameter? Or you intend every "slice" of the file? Can you explain
a little example?
When I receive the file xml to import, I don't know his dimension.
Thanks in advance.
Bruno Stefanutti
"Michael Rys [MSFT]" wrote:
> The best way is to put the expression into a stored procedure and pass the
> value as a paramater (you can use TEXT or NTEXT as the parameter type).
> Best regards
> Michael
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
>
>
|||No I mean the actual content of the XML.
Here is an example:
create procedure example @.x NTEXT
as
declare @.h int
exec sp_xml_preparedocument @.h OUTPUT, @.x
SELECT *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName')
SELECT *
FROM OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
CustomerID varchar(5) '../@.ID',
OrderID varchar(5),
OrderDate datetime)
select *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName') C
JOIN OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
OrderID varchar(5),
OrderDate datetime) O
ON C.ID=O.ParentID
exec sp_xml_removedocument @.h
go
-- The following is a TSQL call to the stored proc. Otherwise you use your
favorite client provider to call the procedure with the parameter
exec example N'<root>
<Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
Country="Switzerland" Phone="555-555555" Fax="555-555555">
<Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="29.46" />
<Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="149.46" />
</Customer>
</root>'
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...[vbcol=seagreen]
> ...When you say "expression" this means I have to pass the path of my xml
> file as a parameter? Or you intend every "slice" of the file? Can you
> explain
> a little example?
> When I receive the file xml to import, I don't know his dimension.
> Thanks in advance.
> Bruno Stefanutti
> "Michael Rys [MSFT]" wrote:
|||I tried using the text type before. It doesnt' allow it. How is it working
for you?
"Michael Rys [MSFT]" wrote:
> No I mean the actual content of the XML.
> Here is an example:
> create procedure example @.x NTEXT
> as
> declare @.h int
> exec sp_xml_preparedocument @.h OUTPUT, @.x
> SELECT *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName')
> SELECT *
> FROM OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> CustomerID varchar(5) '../@.ID',
> OrderID varchar(5),
> OrderDate datetime)
> select *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName') C
> JOIN OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> OrderID varchar(5),
> OrderDate datetime) O
> ON C.ID=O.ParentID
> exec sp_xml_removedocument @.h
> go
> -- The following is a TSQL call to the stored proc. Otherwise you use your
> favorite client provider to call the procedure with the parameter
> exec example N'<root>
> <Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
> ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
> Country="Switzerland" Phone="555-555555" Fax="555-555555">
> <Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
> RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="29.46" />
> <Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
> RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="149.46" />
> </Customer>
> </root>'
>
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
>
>
|||Did you try the example below? Note that the text type can only be used as a
parameter and not a variable in SQL Server 2000 (this will work with SQL
Server 2005).
Best regards
Michael
"Reese77" <Reese77@.discussions.microsoft.com> wrote in message
news:3D38697A-2E7A-4BC2-AABD-6CC8017E32A3@.microsoft.com...[vbcol=seagreen]
>I tried using the text type before. It doesnt' allow it. How is it
>working
> for you?
> "Michael Rys [MSFT]" wrote:
found a lot of documentation about openxml but I've still a doubt...
In this simple example I'm able to load in a table an extract of a xml file:
here you are:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a INSERT statement that uses the OPENXML rowset provider.
INSERT INTO CUSTOMER
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
The question is very simple, probably; how can I manage the file without
"embed it" in to the code between the ' ' (set @.doc='...')?
In fact my file is potentially large and I would like to open the file and
insert the record dinamically without paste it in the procedure code....
Thanks in advance.
Bruno Stefanutti
The best way is to put the expression into a stored procedure and pass the
value as a paramater (you can use TEXT or NTEXT as the parameter type).
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
> Hello; I'm a newby with openxml; I'm sorry in advance for my question.
> I've
> found a lot of documentation about openxml but I've still a doubt...
> In this simple example I'm able to load in a table an extract of a xml
> file:
> here you are:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order CustomerID="VINET" EmployeeID="5"
> OrderDate="1996-07-04T00:00:00">
> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order CustomerID="LILAS" EmployeeID="3"
> OrderDate="1996-08-16T00:00:00">
> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a INSERT statement that uses the OPENXML rowset provider.
> INSERT INTO CUSTOMER
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> The question is very simple, probably; how can I manage the file without
> "embed it" in to the code between the ' ' (set @.doc='...')?
> In fact my file is potentially large and I would like to open the file
> and
> insert the record dinamically without paste it in the procedure code....
> Thanks in advance.
> Bruno Stefanutti
>
>
|||...When you say "expression" this means I have to pass the path of my xml
file as a parameter? Or you intend every "slice" of the file? Can you explain
a little example?
When I receive the file xml to import, I don't know his dimension.
Thanks in advance.
Bruno Stefanutti
"Michael Rys [MSFT]" wrote:
> The best way is to put the expression into a stored procedure and pass the
> value as a paramater (you can use TEXT or NTEXT as the parameter type).
> Best regards
> Michael
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
>
>
|||No I mean the actual content of the XML.
Here is an example:
create procedure example @.x NTEXT
as
declare @.h int
exec sp_xml_preparedocument @.h OUTPUT, @.x
SELECT *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName')
SELECT *
FROM OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
CustomerID varchar(5) '../@.ID',
OrderID varchar(5),
OrderDate datetime)
select *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName') C
JOIN OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
OrderID varchar(5),
OrderDate datetime) O
ON C.ID=O.ParentID
exec sp_xml_removedocument @.h
go
-- The following is a TSQL call to the stored proc. Otherwise you use your
favorite client provider to call the procedure with the parameter
exec example N'<root>
<Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
Country="Switzerland" Phone="555-555555" Fax="555-555555">
<Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="29.46" />
<Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="149.46" />
</Customer>
</root>'
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...[vbcol=seagreen]
> ...When you say "expression" this means I have to pass the path of my xml
> file as a parameter? Or you intend every "slice" of the file? Can you
> explain
> a little example?
> When I receive the file xml to import, I don't know his dimension.
> Thanks in advance.
> Bruno Stefanutti
> "Michael Rys [MSFT]" wrote:
|||I tried using the text type before. It doesnt' allow it. How is it working
for you?
"Michael Rys [MSFT]" wrote:
> No I mean the actual content of the XML.
> Here is an example:
> create procedure example @.x NTEXT
> as
> declare @.h int
> exec sp_xml_preparedocument @.h OUTPUT, @.x
> SELECT *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName')
> SELECT *
> FROM OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> CustomerID varchar(5) '../@.ID',
> OrderID varchar(5),
> OrderDate datetime)
> select *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName') C
> JOIN OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> OrderID varchar(5),
> OrderDate datetime) O
> ON C.ID=O.ParentID
> exec sp_xml_removedocument @.h
> go
> -- The following is a TSQL call to the stored proc. Otherwise you use your
> favorite client provider to call the procedure with the parameter
> exec example N'<root>
> <Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
> ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
> Country="Switzerland" Phone="555-555555" Fax="555-555555">
> <Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
> RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="29.46" />
> <Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
> RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="149.46" />
> </Customer>
> </root>'
>
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
>
>
|||Did you try the example below? Note that the text type can only be used as a
parameter and not a variable in SQL Server 2000 (this will work with SQL
Server 2005).
Best regards
Michael
"Reese77" <Reese77@.discussions.microsoft.com> wrote in message
news:3D38697A-2E7A-4BC2-AABD-6CC8017E32A3@.microsoft.com...[vbcol=seagreen]
>I tried using the text type before. It doesnt' allow it. How is it
>working
> for you?
> "Michael Rys [MSFT]" wrote:
from XML file to sqlserver table
Hello; I'm a newby with openxml; I'm sorry in advance for my question. I've
found a lot of documentation about openxml but I've still a doubt...
In this simple example I'm able to load in a table an extract of a xml file:
here you are:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a INSERT statement that uses the OPENXML rowset provider.
INSERT INTO CUSTOMER
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
The question is very simple, probably; how can I manage the file without
"embed it" in to the code between the ' ' (set @.doc='...')?
In fact my file is potentially large and I would like to open the file and
insert the record dinamically without paste it in the procedure code....
Thanks in advance.
Bruno StefanuttiThe best way is to put the expression into a stored procedure and pass the
value as a paramater (you can use TEXT or NTEXT as the parameter type).
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
> Hello; I'm a newby with openxml; I'm sorry in advance for my question.
> I've
> found a lot of documentation about openxml but I've still a doubt...
> In this simple example I'm able to load in a table an extract of a xml
> file:
> here you are:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order CustomerID="VINET" EmployeeID="5"
> OrderDate="1996-07-04T00:00:00">
> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order CustomerID="LILAS" EmployeeID="3"
> OrderDate="1996-08-16T00:00:00">
> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a INSERT statement that uses the OPENXML rowset provider.
> INSERT INTO CUSTOMER
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> The question is very simple, probably; how can I manage the file without
> "embed it" in to the code between the ' ' (set @.doc='...')?
> In fact my file is potentially large and I would like to open the file
> and
> insert the record dinamically without paste it in the procedure code....
> Thanks in advance.
> Bruno Stefanutti
>
>|||...When you say "expression" this means I have to pass the path of my xml
file as a parameter? Or you intend every "slice" of the file? Can you explai
n
a little example?
When I receive the file xml to import, I don't know his dimension.
Thanks in advance.
Bruno Stefanutti
"Michael Rys [MSFT]" wrote:
> The best way is to put the expression into a stored procedure and pass the
> value as a paramater (you can use TEXT or NTEXT as the parameter type).
> Best regards
> Michael
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
>
>|||No I mean the actual content of the XML.
Here is an example:
create procedure example @.x NTEXT
as
declare @.h int
exec sp_xml_preparedocument @.h OUTPUT, @.x
SELECT *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName')
SELECT *
FROM OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
CustomerID varchar(5) '../@.ID',
OrderID varchar(5),
OrderDate datetime)
select *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName') C
JOIN OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
OrderID varchar(5),
OrderDate datetime) O
ON C.ID=O.ParentID
exec sp_xml_removedocument @.h
go
-- The following is a TSQL call to the stored proc. Otherwise you use your
favorite client provider to call the procedure with the parameter
exec example N'<root>
<Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
Country="Switzerland" Phone="555-555555" Fax="555-555555">
<Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="29.46" />
<Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="149.46" />
</Customer>
</root>'
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
> ...When you say "expression" this means I have to pass the path of my xml
> file as a parameter? Or you intend every "slice" of the file? Can you
> explain
> a little example?
> When I receive the file xml to import, I don't know his dimension.
> Thanks in advance.
> Bruno Stefanutti
> "Michael Rys [MSFT]" wrote:
>|||I tried using the text type before. It doesnt' allow it. How is it working
for you?
"Michael Rys [MSFT]" wrote:
> No I mean the actual content of the XML.
> Here is an example:
> create procedure example @.x NTEXT
> as
> declare @.h int
> exec sp_xml_preparedocument @.h OUTPUT, @.x
> SELECT *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName')
> SELECT *
> FROM OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> CustomerID varchar(5) '../@.ID',
> OrderID varchar(5),
> OrderDate datetime)
> select *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName') C
> JOIN OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> OrderID varchar(5),
> OrderDate datetime) O
> ON C.ID=O.ParentID
> exec sp_xml_removedocument @.h
> go
> -- The following is a TSQL call to the stored proc. Otherwise you use your
> favorite client provider to call the procedure with the parameter
> exec example N'<root>
> <Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
> ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="800
0"
> Country="Switzerland" Phone="555-555555" Fax="555-555555">
> <Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
> RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="29.46" />
> <Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
> RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="149.46" />
> </Customer>
> </root>'
>
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
>
>|||Did you try the example below? Note that the text type can only be used as a
parameter and not a variable in SQL Server 2000 (this will work with SQL
Server 2005).
Best regards
Michael
"Reese77" <Reese77@.discussions.microsoft.com> wrote in message
news:3D38697A-2E7A-4BC2-AABD-6CC8017E32A3@.microsoft.com...
>I tried using the text type before. It doesnt' allow it. How is it
>working
> for you?
> "Michael Rys [MSFT]" wrote:
>
found a lot of documentation about openxml but I've still a doubt...
In this simple example I'm able to load in a table an extract of a xml file:
here you are:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a INSERT statement that uses the OPENXML rowset provider.
INSERT INTO CUSTOMER
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
The question is very simple, probably; how can I manage the file without
"embed it" in to the code between the ' ' (set @.doc='...')?
In fact my file is potentially large and I would like to open the file and
insert the record dinamically without paste it in the procedure code....
Thanks in advance.
Bruno StefanuttiThe best way is to put the expression into a stored procedure and pass the
value as a paramater (you can use TEXT or NTEXT as the parameter type).
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
> Hello; I'm a newby with openxml; I'm sorry in advance for my question.
> I've
> found a lot of documentation about openxml but I've still a doubt...
> In this simple example I'm able to load in a table an extract of a xml
> file:
> here you are:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order CustomerID="VINET" EmployeeID="5"
> OrderDate="1996-07-04T00:00:00">
> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order CustomerID="LILAS" EmployeeID="3"
> OrderDate="1996-08-16T00:00:00">
> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a INSERT statement that uses the OPENXML rowset provider.
> INSERT INTO CUSTOMER
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> The question is very simple, probably; how can I manage the file without
> "embed it" in to the code between the ' ' (set @.doc='...')?
> In fact my file is potentially large and I would like to open the file
> and
> insert the record dinamically without paste it in the procedure code....
> Thanks in advance.
> Bruno Stefanutti
>
>|||...When you say "expression" this means I have to pass the path of my xml
file as a parameter? Or you intend every "slice" of the file? Can you explai
n
a little example?
When I receive the file xml to import, I don't know his dimension.
Thanks in advance.
Bruno Stefanutti
"Michael Rys [MSFT]" wrote:
> The best way is to put the expression into a stored procedure and pass the
> value as a paramater (you can use TEXT or NTEXT as the parameter type).
> Best regards
> Michael
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
>
>|||No I mean the actual content of the XML.
Here is an example:
create procedure example @.x NTEXT
as
declare @.h int
exec sp_xml_preparedocument @.h OUTPUT, @.x
SELECT *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName')
SELECT *
FROM OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
CustomerID varchar(5) '../@.ID',
OrderID varchar(5),
OrderDate datetime)
select *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName') C
JOIN OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
OrderID varchar(5),
OrderDate datetime) O
ON C.ID=O.ParentID
exec sp_xml_removedocument @.h
go
-- The following is a TSQL call to the stored proc. Otherwise you use your
favorite client provider to call the procedure with the parameter
exec example N'<root>
<Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
Country="Switzerland" Phone="555-555555" Fax="555-555555">
<Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="29.46" />
<Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="149.46" />
</Customer>
</root>'
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
> ...When you say "expression" this means I have to pass the path of my xml
> file as a parameter? Or you intend every "slice" of the file? Can you
> explain
> a little example?
> When I receive the file xml to import, I don't know his dimension.
> Thanks in advance.
> Bruno Stefanutti
> "Michael Rys [MSFT]" wrote:
>|||I tried using the text type before. It doesnt' allow it. How is it working
for you?
"Michael Rys [MSFT]" wrote:
> No I mean the actual content of the XML.
> Here is an example:
> create procedure example @.x NTEXT
> as
> declare @.h int
> exec sp_xml_preparedocument @.h OUTPUT, @.x
> SELECT *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName')
> SELECT *
> FROM OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> CustomerID varchar(5) '../@.ID',
> OrderID varchar(5),
> OrderDate datetime)
> select *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName') C
> JOIN OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> OrderID varchar(5),
> OrderDate datetime) O
> ON C.ID=O.ParentID
> exec sp_xml_removedocument @.h
> go
> -- The following is a TSQL call to the stored proc. Otherwise you use your
> favorite client provider to call the procedure with the parameter
> exec example N'<root>
> <Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
> ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="800
0"
> Country="Switzerland" Phone="555-555555" Fax="555-555555">
> <Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
> RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="29.46" />
> <Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
> RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="149.46" />
> </Customer>
> </root>'
>
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
>
>|||Did you try the example below? Note that the text type can only be used as a
parameter and not a variable in SQL Server 2000 (this will work with SQL
Server 2005).
Best regards
Michael
"Reese77" <Reese77@.discussions.microsoft.com> wrote in message
news:3D38697A-2E7A-4BC2-AABD-6CC8017E32A3@.microsoft.com...
>I tried using the text type before. It doesnt' allow it. How is it
>working
> for you?
> "Michael Rys [MSFT]" wrote:
>
from xml file to sql server table
Hello; I'm very newbe about XML and I0m sorry for the question; I would like
to begin the "management" of XML data but I'm a lot
; I would like t
o
start with a simple example about an import/insert process from a xml file t
o
a sql server table. I often read example about, but even only a portion of
the xml file was "hard coded" in the script; I would like to import the whol
e
file, not only a portion.
Best regards and thanks in advance.
Bruno StefanuttiHello consept,
> Hello; I'm very newbe about XML and I0m sorry for the question; I
> would like to begin the "management" of XML data but I'm a lot
>; I would like to start with a simple example about an
> import/insert process from a xml file to a sql server table. I often
> read example about, but even only a portion of the xml file was "hard
> coded" in the script; I would like to import the whole file, not only
> a portion.
Here you go:
drop table dbo.t
go
create table dbo.t(pkid tinyint identity(1,1), x xml)
go
insert into dbo.t(x)
select t.c from
openrowset(bulk 'c:\somefile.xml',SINGLE_BLOB) as t(c)
go
select x from dbo.t
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Dear Kent,
thanks for the help but there is some problems; for example the system said
"type xml unknow in row 3"...Also, in openrowset(bulk...) instruction the
system disagrees..I don't know. Do you have some more simple example? I'm
sorry
Thanks in advance.
Bruno
"Kent Tegels" wrote:
> Hello consept,
>
> Here you go:
> drop table dbo.t
> go
> create table dbo.t(pkid tinyint identity(1,1), x xml)
> go
> insert into dbo.t(x)
> select t.c from
> openrowset(bulk 'c:\somefile.xml',SINGLE_BLOB) as t(c)
> go
> select x from dbo.t
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||You need SQL Server 2005 to get the code below to work.
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:D1FFC724-BD47-43BF-999F-CB954E07F60D@.microsoft.com...
> Dear Kent,
> thanks for the help but there is some problems; for example the system
> said
> "type xml unknow in row 3"...Also, in openrowset(bulk...) instruction the
> system disagrees..I don't know. Do you have some more simple example? I'm
> sorry
> Thanks in advance.
> Bruno
> "Kent Tegels" wrote:
>sql
to begin the "management" of XML data but I'm a lot
; I would like to
start with a simple example about an import/insert process from a xml file t
o
a sql server table. I often read example about, but even only a portion of
the xml file was "hard coded" in the script; I would like to import the whol
e
file, not only a portion.
Best regards and thanks in advance.
Bruno StefanuttiHello consept,
> Hello; I'm very newbe about XML and I0m sorry for the question; I
> would like to begin the "management" of XML data but I'm a lot
>
; I would like to start with a simple example about an> import/insert process from a xml file to a sql server table. I often
> read example about, but even only a portion of the xml file was "hard
> coded" in the script; I would like to import the whole file, not only
> a portion.
Here you go:
drop table dbo.t
go
create table dbo.t(pkid tinyint identity(1,1), x xml)
go
insert into dbo.t(x)
select t.c from
openrowset(bulk 'c:\somefile.xml',SINGLE_BLOB) as t(c)
go
select x from dbo.t
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Dear Kent,
thanks for the help but there is some problems; for example the system said
"type xml unknow in row 3"...Also, in openrowset(bulk...) instruction the
system disagrees..I don't know. Do you have some more simple example? I'm
sorry
Thanks in advance.
Bruno
"Kent Tegels" wrote:
> Hello consept,
>
> Here you go:
> drop table dbo.t
> go
> create table dbo.t(pkid tinyint identity(1,1), x xml)
> go
> insert into dbo.t(x)
> select t.c from
> openrowset(bulk 'c:\somefile.xml',SINGLE_BLOB) as t(c)
> go
> select x from dbo.t
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||You need SQL Server 2005 to get the code below to work.
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:D1FFC724-BD47-43BF-999F-CB954E07F60D@.microsoft.com...
> Dear Kent,
> thanks for the help but there is some problems; for example the system
> said
> "type xml unknow in row 3"...Also, in openrowset(bulk...) instruction the
> system disagrees..I don't know. Do you have some more simple example? I'm
> sorry
> Thanks in advance.
> Bruno
> "Kent Tegels" wrote:
>sql
from xml file to sql server table
Hello; I'm very newbe about XML and I0m sorry for the question; I would like
to begin the "management" of XML data but I'm a lot confused; I would like to
start with a simple example about an import/insert process from a xml file to
a sql server table. I often read example about, but even only a portion of
the xml file was "hard coded" in the script; I would like to import the whole
file, not only a portion.
Best regards and thanks in advance.
Bruno Stefanutti
Hello consept,
> Hello; I'm very newbe about XML and I0m sorry for the question; I
> would like to begin the "management" of XML data but I'm a lot
> confused; I would like to start with a simple example about an
> import/insert process from a xml file to a sql server table. I often
> read example about, but even only a portion of the xml file was "hard
> coded" in the script; I would like to import the whole file, not only
> a portion.
Here you go:
drop table dbo.t
go
create table dbo.t(pkid tinyint identity(1,1), x xml)
go
insert into dbo.t(x)
select t.c from
openrowset(bulk 'c:\somefile.xml',SINGLE_BLOB) as t(c)
go
select x from dbo.t
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Dear Kent,
thanks for the help but there is some problems; for example the system said
"type xml unknow in row 3"...Also, in openrowset(bulk...) instruction the
system disagrees..I don't know. Do you have some more simple example? I'm
sorry
Thanks in advance.
Bruno
"Kent Tegels" wrote:
> Hello consept,
>
> Here you go:
> drop table dbo.t
> go
> create table dbo.t(pkid tinyint identity(1,1), x xml)
> go
> insert into dbo.t(x)
> select t.c from
> openrowset(bulk 'c:\somefile.xml',SINGLE_BLOB) as t(c)
> go
> select x from dbo.t
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||You need SQL Server 2005 to get the code below to work.
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:D1FFC724-BD47-43BF-999F-CB954E07F60D@.microsoft.com...[vbcol=seagreen]
> Dear Kent,
> thanks for the help but there is some problems; for example the system
> said
> "type xml unknow in row 3"...Also, in openrowset(bulk...) instruction the
> system disagrees..I don't know. Do you have some more simple example? I'm
> sorry
> Thanks in advance.
> Bruno
> "Kent Tegels" wrote:
to begin the "management" of XML data but I'm a lot confused; I would like to
start with a simple example about an import/insert process from a xml file to
a sql server table. I often read example about, but even only a portion of
the xml file was "hard coded" in the script; I would like to import the whole
file, not only a portion.
Best regards and thanks in advance.
Bruno Stefanutti
Hello consept,
> Hello; I'm very newbe about XML and I0m sorry for the question; I
> would like to begin the "management" of XML data but I'm a lot
> confused; I would like to start with a simple example about an
> import/insert process from a xml file to a sql server table. I often
> read example about, but even only a portion of the xml file was "hard
> coded" in the script; I would like to import the whole file, not only
> a portion.
Here you go:
drop table dbo.t
go
create table dbo.t(pkid tinyint identity(1,1), x xml)
go
insert into dbo.t(x)
select t.c from
openrowset(bulk 'c:\somefile.xml',SINGLE_BLOB) as t(c)
go
select x from dbo.t
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Dear Kent,
thanks for the help but there is some problems; for example the system said
"type xml unknow in row 3"...Also, in openrowset(bulk...) instruction the
system disagrees..I don't know. Do you have some more simple example? I'm
sorry
Thanks in advance.
Bruno
"Kent Tegels" wrote:
> Hello consept,
>
> Here you go:
> drop table dbo.t
> go
> create table dbo.t(pkid tinyint identity(1,1), x xml)
> go
> insert into dbo.t(x)
> select t.c from
> openrowset(bulk 'c:\somefile.xml',SINGLE_BLOB) as t(c)
> go
> select x from dbo.t
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||You need SQL Server 2005 to get the code below to work.
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:D1FFC724-BD47-43BF-999F-CB954E07F60D@.microsoft.com...[vbcol=seagreen]
> Dear Kent,
> thanks for the help but there is some problems; for example the system
> said
> "type xml unknow in row 3"...Also, in openrowset(bulk...) instruction the
> system disagrees..I don't know. Do you have some more simple example? I'm
> sorry
> Thanks in advance.
> Bruno
> "Kent Tegels" wrote:
From varchar(max) to xml
I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
Column a contains the following data:
Col1;Col2
New York;USA
Rio;Brasil
Tokio;Japan
The first line contains the column header, the following the data.
The data should be transferred to column b with the following xml-structure:
<Col1>New York</Col1><Col2>USA</Col2>
<Col1>Rio</Col1><Col2>Brasil</Col2>
<Col1>Tokio</Col1><Col2>Japan</Col2>
The number of columns and the column names are various.
Any ideas?
Thanks psychodad71
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1It looks like this would require a lot of string manipulation. Although I
believe it could be done with T-SQL, the string functions are a little limit
ed.
I'd suggest you use the CLR.
I'll give it a shot myself when I get some time and I'll post an update.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||I can't think of a nice set-based method of doing this, but you could use
procedural code to do it. I agree with Denis, this should probably be done
in the front end. But that said, here's a little procedural sample. Note
that I don't have SQL 2005 on the computer I'm at right now so I had to put
this thing together on SQL 2000. It should run properly on SQL 2005 as
well. It's *extremely* procedural and assumes that the TestInput table has
a numeric id for each row, row 0 being the column names and all other rows
containing data. The procedural nature of this type of code makes me think
you'd be a lot better off doing it on the front end though:
-- Create a "Numbers" table and an inline UDF that uses it to parse your
-- comma-delimited string. Run this section one time.
SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
INTO Numbers
FROM syscomments a1
CROSS JOIN syscomments a2
-- Add Primary Key to Numbers table
ALTER TABLE Numbers
ALTER COLUMN Number INT NOT NULL
ALTER TABLE Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
-- Create inline UDF
GO
CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
RETURNS TABLE
AS
RETURN (
SELECT Number, LTRIM(RTRIM(CASE Number
WHEN 1 THEN SUBSTRING(@.list, 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - 1
ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
END)
ELSE SUBSTRING(@.list, Number + 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - Number - 1
ELSE LEN(@.list)
END)
END)) AS Value
FROM Numbers
WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
)
GO
-- End of the Numbers table/UDF initialization.
CREATE TABLE TestInput([id] INT PRIMARY KEY,
a VARCHAR(8000),
b VARCHAR(8000))
INSERT INTO TestInput([id], a)
SELECT 0, 'Col1;Col2'
UNION SELECT 1, 'New York;USA'
UNION SELECT 2, 'Rio;Brasil'
UNION SELECT 3, 'Tokio;Japan'
DECLARE @.sql VARCHAR(8000)
DECLARE @.temp_str VARCHAR(8000)
DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[col_name] VARCHAR(8000))
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = 0
INSERT INTO @.cols([col_name])
SELECT Value
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
DECLARE @.col_count INT
SELECT @.col_count = MAX([id_num])
FROM @.cols
DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[value] VARCHAR(8000))
DECLARE @.id INT
SELECT @.id = 1
DECLARE @.i INT
WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
BEGIN
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = @.id
IF NOT(@.temp_str IS NULL)
BEGIN
INSERT INTO @.vals([value])
SELECT [Value]
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
SELECT @.temp_str = ''
SELECT @.i = 1
WHILE @.i <= @.col_count
BEGIN
SELECT @.temp_str = @.temp_str + '<' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.temp_str = @.temp_str +
(
SELECT COALESCE([value], '')
FROM @.vals
WHERE [id_num] = @.i + (@.id - 1) * @.col_count
)
SELECT @.temp_str = @.temp_str + '</' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.i = @.i + 1
END
UPDATE TestInput
SET b = @.temp_str
WHERE [id] = @.id
END
SELECT @.id = @.id + 1
END
SELECT *
FROM @.vals
SELECT *
FROM TestInput
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||Ooops, the "syscomments" references will need to be changed for SQL 2005 to
"sys.comments".
"Mike C#" <xyz@.xyz.com> wrote in message
news:ORxRYYwlGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I can't think of a nice set-based method of doing this, but you could use
>procedural code to do it. I agree with Denis, this should probably be done
>in the front end. But that said, here's a little procedural sample. Note
>that I don't have SQL 2005 on the computer I'm at right now so I had to put
>this thing together on SQL 2000. It should run properly on SQL 2005 as
>well. It's *extremely* procedural and assumes that the TestInput table has
>a numeric id for each row, row 0 being the column names and all other rows
>containing data. The procedural nature of this type of code makes me think
>you'd be a lot better off doing it on the front end though:
> -- Create a "Numbers" table and an inline UDF that uses it to parse your
> -- comma-delimited string. Run this section one time.
> SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
> INTO Numbers
> FROM syscomments a1
> CROSS JOIN syscomments a2
> -- Add Primary Key to Numbers table
> ALTER TABLE Numbers
> ALTER COLUMN Number INT NOT NULL
> ALTER TABLE Numbers
> ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
> -- Create inline UDF
> GO
> CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
> RETURNS TABLE
> AS
> RETURN (
> SELECT Number, LTRIM(RTRIM(CASE Number
> WHEN 1 THEN SUBSTRING(@.list, 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - 1
> ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
> END)
> ELSE SUBSTRING(@.list, Number + 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - Number - 1
> ELSE LEN(@.list)
> END)
> END)) AS Value
> FROM Numbers
> WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
> )
> GO
> -- End of the Numbers table/UDF initialization.
> CREATE TABLE TestInput([id] INT PRIMARY KEY,
> a VARCHAR(8000),
> b VARCHAR(8000))
> INSERT INTO TestInput([id], a)
> SELECT 0, 'Col1;Col2'
> UNION SELECT 1, 'New York;USA'
> UNION SELECT 2, 'Rio;Brasil'
> UNION SELECT 3, 'Tokio;Japan'
> DECLARE @.sql VARCHAR(8000)
> DECLARE @.temp_str VARCHAR(8000)
> DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [col_name] VARCHAR(8000))
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = 0
> INSERT INTO @.cols([col_name])
> SELECT Value
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> DECLARE @.col_count INT
> SELECT @.col_count = MAX([id_num])
> FROM @.cols
> DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [value] VARCHAR(8000))
> DECLARE @.id INT
> SELECT @.id = 1
> DECLARE @.i INT
> WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
> BEGIN
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = @.id
> IF NOT(@.temp_str IS NULL)
> BEGIN
> INSERT INTO @.vals([value])
> SELECT [Value]
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> SELECT @.temp_str = ''
> SELECT @.i = 1
> WHILE @.i <= @.col_count
> BEGIN
> SELECT @.temp_str = @.temp_str + '<' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.temp_str = @.temp_str +
> (
> SELECT COALESCE([value], '')
> FROM @.vals
> WHERE [id_num] = @.i + (@.id - 1) * @.col_count
> )
> SELECT @.temp_str = @.temp_str + '</' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.i = @.i + 1
> END
> UPDATE TestInput
> SET b = @.temp_str
> WHERE [id] = @.id
> END
> SELECT @.id = @.id + 1
> END
> SELECT *
> FROM @.vals
> SELECT *
> FROM TestInput
>
>
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:6232e7ee08949@.uwe...
>|||psychodad71 via webservertalk.com wrote:
> I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
If your database supports access to external scripting languages, dump
column a out and pass it through the following filter
awk -F\; 'BEGIN {ORS=""}
{if(NR==1)n=split($0,gi);else{for(i=1;i<=NF;++i)print "<" gi[i] ">" $i
"</" gi[i] ">";print "\n"}}'
and read the result into column b. The GNU awk processor for Windows can
be downloaded from http://gnuwin32.sourceforge.net/packages/gawk.htm
///Peter|||Your table will need something to identity the first row, so I added an iden
tity
col. The first row inserted is assumed to be a column header.
CREATE TABLE #t1
(
id int identity primary key,
city VARCHAR(50),
xdata xml DEFAULT ''
)
INSERT INTO #t1 (city) values ('Col1;Col2;Col3;Col4')
INSERT INTO #t1 (city) values ('New York;Boston;Chicago;USA')
INSERT INTO #t1 (city) values ('Rio;Bla;Sao Paulo;Brasil')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;EF;Japan')
The city column has a compound value in it. You can use recursion to "unflat
ten"
this into a table with one row per city. Once you have done that you can
compose that table into xml, using recursion again.
-- start by making CTE of elementNames
WITH elementNames
AS
(
SELECT TOP(1) id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1)
as colName, RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remai
n
from #t1
ORDER BY id
UNION ALL
SELECT t.id, en.colNum + 1 as colNum, LEFT(en.remain, CHARINDEX(';', en.rema
in)-1)
as single, RIGHT(en.remain, LEN(en.remain)-CHARINDEX(';', en.remain)) as
remain from elementNames en
JOIN #t1 AS t ON t.id = en.id
WHERE LEN(remain)>0
),
-- now recurse to "unflatten" the composite value in the city column
pos
AS
(
-- find the first city
SELECT id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1) as single
,
RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remain from #t1
WHERE id not in (select id from elementNames)
UNION ALL
-- find the rest
SELECT id, colNum + 1 as colNum,
LEFT(remain, CHARINDEX(';', remain)-1) as single, RIGHT(remain, LEN(remain)-
CHARINDEX(';',
remain))
as remain from pos
where LEN(remain) > 0
and id not in (select id from elementNames)
),
-- now compose xml of of the expanded table
compose
as
(
SELECT p.id, p.colNum, CAST('<' + e.colName + '>' + p.single + '</' + e.colN
ame
+ '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
where p.colNum = 1
UNION ALL
SELECT p.id, p.colNum, CAST(c.xdata + '<' + e.colName + '>' + p.single +
'</' + e.colName + '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
JOIN compose AS c on p.colNum = c.colNum+1 and p.id = c.id
)
-- use composed xml to update the original table
UPDATE #t1 set xdata = (SELECT xdata from compose where #t1.id = compose.id
AND compose.colNum = (SELECT MAX(colNum) from compose as c WHERE c.id = #t1.
id)
)
Then to test the results:
SELECT * FROM #t1
1 Col1;Col2;Col3;Col4 NULL
2 New York;Boston;Chicago;USA <Col1>New
York</Col1><Col2>Boston</Col2><Col3>Chicago</Col3><Col4>USA</Col4>
3 Rio;Bla;Sao Paulo;Brasil <Col1>Rio</Co
l1><Col2>Bla</Col2><Col3>Sao
Paulo</Col3><Col4>Brasil</Col4>
4 Tokio;Nagasaki;ABCD;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>Japan</Col4>
5 Tokio;Nagasaki;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>Japan</Col3>
6 Tokio;Nagasaki;ABCD;EF;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>EF</Col4>
Note that this works as even when the number of column headings do not match
the number of cities, though the results might not be what you want.
Dan
> I have a table with 2 columns. Column a(varchar(max)) and column
> b(xml). Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
>|||For some reason I see that some of the xml like stuff I have shown in this q
uery seems to be lost once it is posted. I have attached a text file version
of it.
Dan|||Alternatively, using a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
you can do this
;
with Headers(id,rn,ColName)
as(
select id,
rank() over(order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id=1),
Cities(id,rn,City)
as(
select id,
rank() over(partition by id order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id>1)
update #t1
set xdata=(select cast('<'+h.ColName+'>'+c.City+'</'+h.ColName+'>' as
xml)
from Headers h
inner join Cities c on c.rn=h.rn
where c.id=#t1.id
for xml path(''))
select * from #t1
Regards
Mark|||Slight correction.
where c.id=#t1.id
for xml path(''))
should be
where c.id=#t1.id
order by c.rn
for xml path(''))
Column a contains the following data:
Col1;Col2
New York;USA
Rio;Brasil
Tokio;Japan
The first line contains the column header, the following the data.
The data should be transferred to column b with the following xml-structure:
<Col1>New York</Col1><Col2>USA</Col2>
<Col1>Rio</Col1><Col2>Brasil</Col2>
<Col1>Tokio</Col1><Col2>Japan</Col2>
The number of columns and the column names are various.
Any ideas?
Thanks psychodad71
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1It looks like this would require a lot of string manipulation. Although I
believe it could be done with T-SQL, the string functions are a little limit
ed.
I'd suggest you use the CLR.
I'll give it a shot myself when I get some time and I'll post an update.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||I can't think of a nice set-based method of doing this, but you could use
procedural code to do it. I agree with Denis, this should probably be done
in the front end. But that said, here's a little procedural sample. Note
that I don't have SQL 2005 on the computer I'm at right now so I had to put
this thing together on SQL 2000. It should run properly on SQL 2005 as
well. It's *extremely* procedural and assumes that the TestInput table has
a numeric id for each row, row 0 being the column names and all other rows
containing data. The procedural nature of this type of code makes me think
you'd be a lot better off doing it on the front end though:
-- Create a "Numbers" table and an inline UDF that uses it to parse your
-- comma-delimited string. Run this section one time.
SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
INTO Numbers
FROM syscomments a1
CROSS JOIN syscomments a2
-- Add Primary Key to Numbers table
ALTER TABLE Numbers
ALTER COLUMN Number INT NOT NULL
ALTER TABLE Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
-- Create inline UDF
GO
CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
RETURNS TABLE
AS
RETURN (
SELECT Number, LTRIM(RTRIM(CASE Number
WHEN 1 THEN SUBSTRING(@.list, 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - 1
ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
END)
ELSE SUBSTRING(@.list, Number + 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - Number - 1
ELSE LEN(@.list)
END)
END)) AS Value
FROM Numbers
WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
)
GO
-- End of the Numbers table/UDF initialization.
CREATE TABLE TestInput([id] INT PRIMARY KEY,
a VARCHAR(8000),
b VARCHAR(8000))
INSERT INTO TestInput([id], a)
SELECT 0, 'Col1;Col2'
UNION SELECT 1, 'New York;USA'
UNION SELECT 2, 'Rio;Brasil'
UNION SELECT 3, 'Tokio;Japan'
DECLARE @.sql VARCHAR(8000)
DECLARE @.temp_str VARCHAR(8000)
DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[col_name] VARCHAR(8000))
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = 0
INSERT INTO @.cols([col_name])
SELECT Value
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
DECLARE @.col_count INT
SELECT @.col_count = MAX([id_num])
FROM @.cols
DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[value] VARCHAR(8000))
DECLARE @.id INT
SELECT @.id = 1
DECLARE @.i INT
WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
BEGIN
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = @.id
IF NOT(@.temp_str IS NULL)
BEGIN
INSERT INTO @.vals([value])
SELECT [Value]
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
SELECT @.temp_str = ''
SELECT @.i = 1
WHILE @.i <= @.col_count
BEGIN
SELECT @.temp_str = @.temp_str + '<' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.temp_str = @.temp_str +
(
SELECT COALESCE([value], '')
FROM @.vals
WHERE [id_num] = @.i + (@.id - 1) * @.col_count
)
SELECT @.temp_str = @.temp_str + '</' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.i = @.i + 1
END
UPDATE TestInput
SET b = @.temp_str
WHERE [id] = @.id
END
SELECT @.id = @.id + 1
END
SELECT *
FROM @.vals
SELECT *
FROM TestInput
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||Ooops, the "syscomments" references will need to be changed for SQL 2005 to
"sys.comments".
"Mike C#" <xyz@.xyz.com> wrote in message
news:ORxRYYwlGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I can't think of a nice set-based method of doing this, but you could use
>procedural code to do it. I agree with Denis, this should probably be done
>in the front end. But that said, here's a little procedural sample. Note
>that I don't have SQL 2005 on the computer I'm at right now so I had to put
>this thing together on SQL 2000. It should run properly on SQL 2005 as
>well. It's *extremely* procedural and assumes that the TestInput table has
>a numeric id for each row, row 0 being the column names and all other rows
>containing data. The procedural nature of this type of code makes me think
>you'd be a lot better off doing it on the front end though:
> -- Create a "Numbers" table and an inline UDF that uses it to parse your
> -- comma-delimited string. Run this section one time.
> SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
> INTO Numbers
> FROM syscomments a1
> CROSS JOIN syscomments a2
> -- Add Primary Key to Numbers table
> ALTER TABLE Numbers
> ALTER COLUMN Number INT NOT NULL
> ALTER TABLE Numbers
> ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
> -- Create inline UDF
> GO
> CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
> RETURNS TABLE
> AS
> RETURN (
> SELECT Number, LTRIM(RTRIM(CASE Number
> WHEN 1 THEN SUBSTRING(@.list, 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - 1
> ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
> END)
> ELSE SUBSTRING(@.list, Number + 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - Number - 1
> ELSE LEN(@.list)
> END)
> END)) AS Value
> FROM Numbers
> WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
> )
> GO
> -- End of the Numbers table/UDF initialization.
> CREATE TABLE TestInput([id] INT PRIMARY KEY,
> a VARCHAR(8000),
> b VARCHAR(8000))
> INSERT INTO TestInput([id], a)
> SELECT 0, 'Col1;Col2'
> UNION SELECT 1, 'New York;USA'
> UNION SELECT 2, 'Rio;Brasil'
> UNION SELECT 3, 'Tokio;Japan'
> DECLARE @.sql VARCHAR(8000)
> DECLARE @.temp_str VARCHAR(8000)
> DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [col_name] VARCHAR(8000))
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = 0
> INSERT INTO @.cols([col_name])
> SELECT Value
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> DECLARE @.col_count INT
> SELECT @.col_count = MAX([id_num])
> FROM @.cols
> DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [value] VARCHAR(8000))
> DECLARE @.id INT
> SELECT @.id = 1
> DECLARE @.i INT
> WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
> BEGIN
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = @.id
> IF NOT(@.temp_str IS NULL)
> BEGIN
> INSERT INTO @.vals([value])
> SELECT [Value]
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> SELECT @.temp_str = ''
> SELECT @.i = 1
> WHILE @.i <= @.col_count
> BEGIN
> SELECT @.temp_str = @.temp_str + '<' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.temp_str = @.temp_str +
> (
> SELECT COALESCE([value], '')
> FROM @.vals
> WHERE [id_num] = @.i + (@.id - 1) * @.col_count
> )
> SELECT @.temp_str = @.temp_str + '</' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.i = @.i + 1
> END
> UPDATE TestInput
> SET b = @.temp_str
> WHERE [id] = @.id
> END
> SELECT @.id = @.id + 1
> END
> SELECT *
> FROM @.vals
> SELECT *
> FROM TestInput
>
>
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:6232e7ee08949@.uwe...
>|||psychodad71 via webservertalk.com wrote:
> I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
If your database supports access to external scripting languages, dump
column a out and pass it through the following filter
awk -F\; 'BEGIN {ORS=""}
{if(NR==1)n=split($0,gi);else{for(i=1;i<=NF;++i)print "<" gi[i] ">" $i
"</" gi[i] ">";print "\n"}}'
and read the result into column b. The GNU awk processor for Windows can
be downloaded from http://gnuwin32.sourceforge.net/packages/gawk.htm
///Peter|||Your table will need something to identity the first row, so I added an iden
tity
col. The first row inserted is assumed to be a column header.
CREATE TABLE #t1
(
id int identity primary key,
city VARCHAR(50),
xdata xml DEFAULT ''
)
INSERT INTO #t1 (city) values ('Col1;Col2;Col3;Col4')
INSERT INTO #t1 (city) values ('New York;Boston;Chicago;USA')
INSERT INTO #t1 (city) values ('Rio;Bla;Sao Paulo;Brasil')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;EF;Japan')
The city column has a compound value in it. You can use recursion to "unflat
ten"
this into a table with one row per city. Once you have done that you can
compose that table into xml, using recursion again.
-- start by making CTE of elementNames
WITH elementNames
AS
(
SELECT TOP(1) id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1)
as colName, RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remai
n
from #t1
ORDER BY id
UNION ALL
SELECT t.id, en.colNum + 1 as colNum, LEFT(en.remain, CHARINDEX(';', en.rema
in)-1)
as single, RIGHT(en.remain, LEN(en.remain)-CHARINDEX(';', en.remain)) as
remain from elementNames en
JOIN #t1 AS t ON t.id = en.id
WHERE LEN(remain)>0
),
-- now recurse to "unflatten" the composite value in the city column
pos
AS
(
-- find the first city
SELECT id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1) as single
,
RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remain from #t1
WHERE id not in (select id from elementNames)
UNION ALL
-- find the rest
SELECT id, colNum + 1 as colNum,
LEFT(remain, CHARINDEX(';', remain)-1) as single, RIGHT(remain, LEN(remain)-
CHARINDEX(';',
remain))
as remain from pos
where LEN(remain) > 0
and id not in (select id from elementNames)
),
-- now compose xml of of the expanded table
compose
as
(
SELECT p.id, p.colNum, CAST('<' + e.colName + '>' + p.single + '</' + e.colN
ame
+ '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
where p.colNum = 1
UNION ALL
SELECT p.id, p.colNum, CAST(c.xdata + '<' + e.colName + '>' + p.single +
'</' + e.colName + '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
JOIN compose AS c on p.colNum = c.colNum+1 and p.id = c.id
)
-- use composed xml to update the original table
UPDATE #t1 set xdata = (SELECT xdata from compose where #t1.id = compose.id
AND compose.colNum = (SELECT MAX(colNum) from compose as c WHERE c.id = #t1.
id)
)
Then to test the results:
SELECT * FROM #t1
1 Col1;Col2;Col3;Col4 NULL
2 New York;Boston;Chicago;USA <Col1>New
York</Col1><Col2>Boston</Col2><Col3>Chicago</Col3><Col4>USA</Col4>
3 Rio;Bla;Sao Paulo;Brasil <Col1>Rio</Co
l1><Col2>Bla</Col2><Col3>Sao
Paulo</Col3><Col4>Brasil</Col4>
4 Tokio;Nagasaki;ABCD;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>Japan</Col4>
5 Tokio;Nagasaki;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>Japan</Col3>
6 Tokio;Nagasaki;ABCD;EF;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>EF</Col4>
Note that this works as even when the number of column headings do not match
the number of cities, though the results might not be what you want.
Dan
> I have a table with 2 columns. Column a(varchar(max)) and column
> b(xml). Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
>|||For some reason I see that some of the xml like stuff I have shown in this q
uery seems to be lost once it is posted. I have attached a text file version
of it.
Dan|||Alternatively, using a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
you can do this
;
with Headers(id,rn,ColName)
as(
select id,
rank() over(order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id=1),
Cities(id,rn,City)
as(
select id,
rank() over(partition by id order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id>1)
update #t1
set xdata=(select cast('<'+h.ColName+'>'+c.City+'</'+h.ColName+'>' as
xml)
from Headers h
inner join Cities c on c.rn=h.rn
where c.id=#t1.id
for xml path(''))
select * from #t1
Regards
Mark|||Slight correction.
where c.id=#t1.id
for xml path(''))
should be
where c.id=#t1.id
order by c.rn
for xml path(''))
from SQL 2005 to SQL 2000
Is there an easy way to import table data from SQL 2005 to SQL 2000?
Here's the story:
I want to use SQL 2005 b/c I like varchar(max) over TEXT and several other features. However there is another developer with a SQL 2000 DB who needs to be able to pull data from my tables. If I use something like varchar(max), xml, etc what would the other developer need to do to import my data?
I know that going in the reverse direction is possible, pushing data from a SQL 2005 DB to a SQL 2000 ( http://msdn2.microsoft.com/en
I'm mostly a programmer slowly turning into a dba so please excuse me if I'm missing something obvious.
Thanks.
--Alex
Alex,
If you want to transfer data from SQL 2005 database into SQL 2000 database BCP as you pointed out is the only option. You can use bcp from SQL 2000 installation and it will see datatypes that are compatible with SQL 2000. varchar(max) and xml I believe will be seen as text.
If you want to access data from SQL 2005 instance on SQL 2000 instance (without moving the data between instances) you can do so with linked servers with the same restriction as above (new datatypes will be seen as old ones on SQL 2000).
Regards,
Boris.
From Select to Update
I have a sales table that has a customer_number, salesman_number and sales data (like item, quantity, etc), there is a customer table that has the customer_number, name, address, etc. and salesman_number, with the passign of time salesmen come and go and the customer service department is in charge of modifying the customer table and assign the new salesman number to them. All the previous sales have to be reasigned to this new salesman even though it wasnt him the one that sold the items (I know, I know, thats the way the company requieres it, this is an old system). Now to my question:
I have this select query that gives me the rows from table sales that have to be modified:
SELECT C.sales_rep,S.salesrep,S.*
FROM SALES AS S
INNER JOIN customer AS C
ON C.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_no AND
C.cust_sffx = S.cust_sffx AND
C.division = S.division AND
C.sales_rep != S.salesrep
WHERE S.month = 2 AND
S.year = 2005
ORDER BY C.sales_rep
I just want to know if my approach to convert this query from an update to a select is ok:
UPDATE SALES AS S
SET S.salesrep = C.sales_rep
INNER JOIN customer AS C
ON C.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_no AND
C.cust_sffx = S.cust_sffx AND
C.division = S.division AND
C.sales_rep != S.salesrep
WHERE S.month = 1 AND
S.year = 2004
Thanks for your help
Luis TorresMade some changes to the SQL and it parsed ok, going to try it now:
UPDATE SALES
SET SALES.salesrep = C.sales_rep
FROM SALES AS S
INNER JOIN customer AS C
ON C.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = SALES.site_code AND
C.cust_no = SALES.cust_no AND
C.cust_sffx = SALES.cust_sffx AND
C.division = SALES.division AND
C.sales_rep != SALES.salesrep
WHERE SALES.month = 1 AND
SALES.year = 2004|||Final version, it worked perfectly :)
UPDATE SALES
SET SALES.salesrep = C.sales_rep
FROM SALES AS S
INNER JOIN customer AS C
ON C.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_no AND
C.cust_sffx = S.cust_sffx AND
C.division = S.division AND
C.sales_rep != S.salesrep
WHERE S.month = 1 AND
S.year = 2004|||Glad we could help. ;)|||Lol :) :) :)|||Can you believe that the last dba had a stored procedure with 2 cursors (one inside the other) to do this? The process lasted 21+ hours and it was part of our monthly load. I managed to reduce the time from 23+ hours total to 45 minutes, all the managers are jumping up and down ;) hopefully they will offer me a permanent position here when my contract ends in May, I really enjoy working for this company. :)|||Good luck!
I work as a consultant, and given the number of bad DBAs circulating around the workforce, its pretty common to be able to walk into a company and look like a miracle worker!
I have this select query that gives me the rows from table sales that have to be modified:
SELECT C.sales_rep,S.salesrep,S.*
FROM SALES AS S
INNER JOIN customer AS C
ON C.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_no AND
C.cust_sffx = S.cust_sffx AND
C.division = S.division AND
C.sales_rep != S.salesrep
WHERE S.month = 2 AND
S.year = 2005
ORDER BY C.sales_rep
I just want to know if my approach to convert this query from an update to a select is ok:
UPDATE SALES AS S
SET S.salesrep = C.sales_rep
INNER JOIN customer AS C
ON C.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_no AND
C.cust_sffx = S.cust_sffx AND
C.division = S.division AND
C.sales_rep != S.salesrep
WHERE S.month = 1 AND
S.year = 2004
Thanks for your help
Luis TorresMade some changes to the SQL and it parsed ok, going to try it now:
UPDATE SALES
SET SALES.salesrep = C.sales_rep
FROM SALES AS S
INNER JOIN customer AS C
ON C.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = SALES.site_code AND
C.cust_no = SALES.cust_no AND
C.cust_sffx = SALES.cust_sffx AND
C.division = SALES.division AND
C.sales_rep != SALES.salesrep
WHERE SALES.month = 1 AND
SALES.year = 2004|||Final version, it worked perfectly :)
UPDATE SALES
SET SALES.salesrep = C.sales_rep
FROM SALES AS S
INNER JOIN customer AS C
ON C.Active_Flag = 'A' AND
C.sales_rep != 'XXXX' AND
C.site_code = S.site_code AND
C.cust_no = S.cust_no AND
C.cust_sffx = S.cust_sffx AND
C.division = S.division AND
C.sales_rep != S.salesrep
WHERE S.month = 1 AND
S.year = 2004|||Glad we could help. ;)|||Lol :) :) :)|||Can you believe that the last dba had a stored procedure with 2 cursors (one inside the other) to do this? The process lasted 21+ hours and it was part of our monthly load. I managed to reduce the time from 23+ hours total to 45 minutes, all the managers are jumping up and down ;) hopefully they will offer me a permanent position here when my contract ends in May, I really enjoy working for this company. :)|||Good luck!
I work as a consultant, and given the number of bad DBAs circulating around the workforce, its pretty common to be able to walk into a company and look like a miracle worker!
Monday, March 19, 2012
from excel to sqlServer
I am having An excel file containing name and phone numberHow can I insert this data to a database table (sqlserver2005)?Hope some one can help mesujith
Do you have SQL Server Management Studio installed? If you do, you can import your Excel spreedsheet to SQL Server as a table.
1.Right click on your database name, follow the Tasks>>Import Data...;
2. Choose excel as you data source; browser to where your excel file is;
3. follow along the instruction on following windows and click NEXTs until you finish.
If you have problem to finsh, please post back here.
|||Hi,
Yes you can import export wizard as mentioned in the previous post or you can take a look at this thread:http://forums.asp.net/thread/1442470.aspx
AndHow to import data from Excel to SQL Server demonstrates how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases by using a variety of methods
Hope it helps.
Subscribe to:
Posts (Atom)