Showing posts with label text. Show all posts
Showing posts with label text. Show all posts

Friday, March 23, 2012

Optimize Full Text Search

I have a table that currently has a FullText Index on one of the columns. We are currently adding about 500,000 records a day, so it is growing fairly quickly. we're curretly sitting at around 75 million records, and the performance of the Full Text index is horrible. All of the records are bulk loaded once per day, and the Catalog automatically tracks and applies changes, so I know the Index is up to date. I've gone in and looked a the properties several times, and there are never any pending changes, so I don't think my issue is with loading the data.

Everything I've read so far tells me that the slow down is directly proportional to the amount of results returned from the Full Text Search and that I should limit it based on the items Rank. Unfortunately, in addition to the FTS I have other criteria that need to be met. (namely a date range, transaction type, sender, receiver, etc...) Each query is different, but the Text Field is just part of the equation, so I can't filter out any results using the RANK option in the query.

I am currently on a SQL 2005 Standard instance, so Partitioned Tables isn't an option yet. (Our other production environement is 2005 Enterprise, so I'm working on getting data copied over there to test with.) I'm looking at creating a partition on the data so that there are 52 partitions, one for each week of the year. That will split the underlying table up pretty well, but will that affect the Full Text Search catalog in any way?

I'm afraid that I'm going to do all this work, and in the long run the full text search will still search the entire table, not just the necessary partition. Has anyone else partitioned a table with a Full Text search field on it? And what kind of performance impact has it had?any ideas?sql

Wednesday, March 21, 2012

Optimization gurus: Help with varchar vs. text fields decision

Hi, I'm trying to improve performance for a db that stores messages. The message is currently stored in an ntext field, but when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I don't want to actually limit the message size, but it seems like I might get much better performance using a varchar(3000) field to hold most of the messages, and a separate text field just used for those 3% that really are long. Is this a good idea? If so, is it better to put the Message and LongMessage fields in the same table; or, have a separate table to hold the long messages? If it is in a separate table, it would need to be left joined with the message table each time messages are retrieved.

Also -- I am getting about 700 new messages daily, and right now have over 150,000 messages stored. The vast majority of activity involves new messages. Is this a good situation to look at using horizontal partitioning?

Thanks for any help, I don't really have anyone to discuss this with and it is really helpful to get some other views!!

Are you able to upgrade to SQL2005? VARCHAR(max) would be a simple solution to your problem.

|||

Yay, I'm already on SQL server 2005, so I could use varchar(max) -- now that I've heard of it! Are there performance issues to be aware of with max? Any drawback to a design where the row size will vary wildly from row to row??

|||

Celestine:

when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I calculated wrong -- it is an ntext field, so each char is two bytes, not one byte. Meaning 97% of the messages are actually less than 1500 characters, not 3000. All the messages are in English, so I'm not going to continue using ntext or nvarchar.

|||

Varchar(max) allows rows to span physical blocks, hence no row length restriction. Why not look it up on Books-On_line BOL?

|||

I understand that there is no length restriction, I looked it up right away; thanks for making me aware of the max option. What I am asking about is whether there are performance implications to consider with using varchar(max), when you are hoping to get multiple records to fit on a data page.


|||

With varchar(max) most of the records will be fetched with a single read whereas for ntext, two reads will be required for every record irrespective of its size.

Monday, March 12, 2012

Optimal storage of a lot of text records

Hi,
I have the availability of a database of 100mb. I need to store large
amounts of forum messages, the more the better.Currently every table
row has 4 small nvarchar fields, and a ntext for the body of the forum
message.
This way every row is about 4 kbyte in size. That means I am able to
store "only" 25,600 forum messages.
Is there a more optimal solution so I can put more messages in this
database?
WardWard
Do you say that a database cannot grow more than 100MB?
Is it by defenition?
As an alterrnative you store the messages at filesystem and read them when
you need
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142579469.310833.81370@.p10g2000cwp.googlegroups.com...
> Hi,
> I have the availability of a database of 100mb. I need to store large
> amounts of forum messages, the more the better.Currently every table
> row has 4 small nvarchar fields, and a ntext for the body of the forum
> message.
> This way every row is about 4 kbyte in size. That means I am able to
> store "only" 25,600 forum messages.
> Is there a more optimal solution so I can put more messages in this
> database?
> Ward
>|||Yes, it cannot grow larger. I do have around 500 mb storage space. The
downside of storing it on the filesystem is that I won't be able to use
full-text search. Or doesn't have that to be a problem?|||Ward
> downside of storing it on the filesystem is that I won't be able to use
> full-text search. Or doesn't have that to be a problem?
>
If does not relate . In fact you need a storage SAN probably to deal with
your issue as well as to allow growing your database
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142581209.270659.116510@.u72g2000cwu.googlegroups.com...
> Yes, it cannot grow larger. I do have around 500 mb storage space. The
> downside of storing it on the filesystem is that I won't be able to use
> full-text search. Or doesn't have that to be a problem?
>|||Sorry, should be IT does not relate
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OaGQ5bZSGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Ward
> If does not relate . In fact you need a storage SAN probably to deal with
> your issue as well as to allow growing your database
>
> "Ward Bekker" <w.bekker@.gmail.com> wrote in message
> news:1142581209.270659.116510@.u72g2000cwu.googlegroups.com...
>|||What is your expected database size? Perhaps you simply need more space.
ML
http://milambda.blogspot.com/|||So, you say it's possible to have full-text search on real files from
Sql Server? If so, could you eleborate?|||> So, you say it's possible to have full-text search on real files from
> Sql Server? If so, could you eleborate?
>
If the file is located in SQL Server's table
Well, you will be better of to ask the question in full-text news group, I
have played with full-text very little
Also , there is pretty good article in the BOL how to create/work with
full-text in SQL Server
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142583077.165690.319440@.p10g2000cwp.googlegroups.com...
> So, you say it's possible to have full-text search on real files from
> Sql Server? If so, could you eleborate?
>|||The nvarchar and ntext datatypes support the unicode character set, but
store 2 bytes per character instead of 1 byte per character for varchar and
text. However, switching to varchar and text would only double the capacity
to 50,000 messages; which would not be any real order of magnitude.
You can store the text of the messages in seperate text files with the
naming convention based on the primary key of the message. For example,
message id #120455 would have a related file called 00120455.txt. How you
relate the text files with the messages is an application programming issue.
From what you describe, it sounds like a 3rd party hosted database. Do you
have the option of just paying for the additional storage?
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142579469.310833.81370@.p10g2000cwp.googlegroups.com...
> Hi,
> I have the availability of a database of 100mb. I need to store large
> amounts of forum messages, the more the better.Currently every table
> row has 4 small nvarchar fields, and a ntext for the body of the forum
> message.
> This way every row is about 4 kbyte in size. That means I am able to
> store "only" 25,600 forum messages.
> Is there a more optimal solution so I can put more messages in this
> database?
> Ward
>|||JT,
Yes, it's a hosted database. I can pay for additional storage, but I
want to first make sure that I have a optimal SQL Server config so i
get more bang per buck ;-)
Tnx,
Ward

Wednesday, March 7, 2012

Operations on Parameters

Hi
I attempted to perform a simple mathematical operation (like dividing 10 by
4) in a text box of a Table or body of a report and I end up with an integer
result (10\4 gives me 2.0). I used convert method, CDbl method and I still
get the same result. I formated the text box as N5, C2 and other formats but
still the same result.
The original problem is that the same thing happens with a report parameter
when I try to use it in calculations but by troubleshooting the problem I
realized that it happens in any text box.
What am I doing wrong?
Thanks
ShawnInteresting, I was going to suggest trying 10.0/4.0, but after adding
an expression "=10/4" to a text box I see the result is 2.5. Is you
operation in an expression or in custom code?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
<ShawnKralj@.discussions.microsoft.com> wrote:
>Hi
>I attempted to perform a simple mathematical operation (like dividing 10 by
>4) in a text box of a Table or body of a report and I end up with an integer
>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>get the same result. I formated the text box as N5, C2 and other formats but
>still the same result.
>The original problem is that the same thing happens with a report parameter
>when I try to use it in calculations but by troubleshooting the problem I
>realized that it happens in any text box.
>What am I doing wrong?
>Thanks
>Shawn|||>10\4 gives me 2.0
If you are really using \ then you are getting the correct result. The
'backslash' operator returns only the integer portion of the result.
If you're expecting 2.5 as the result, then you have to use / instead.
On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
<ShawnKralj@.discussions.microsoft.com> wrote:
>Hi
>I attempted to perform a simple mathematical operation (like dividing 10 by
>4) in a text box of a Table or body of a report and I end up with an integer
>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>get the same result. I formated the text box as N5, C2 and other formats but
>still the same result.
>The original problem is that the same thing happens with a report parameter
>when I try to use it in calculations but by troubleshooting the problem I
>realized that it happens in any text box.
>What am I doing wrong?
>Thanks
>Shawn|||Oooh , good catch.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Sun, 14 Nov 2004 23:17:36 -0500, Dan Knight
<dknight@.REMOVEmullinixpackages.THIScom> wrote:
>>10\4 gives me 2.0
>If you are really using \ then you are getting the correct result. The
>'backslash' operator returns only the integer portion of the result.
>If you're expecting 2.5 as the result, then you have to use / instead.
>On Fri, 12 Nov 2004 13:40:08 -0800, "Shawn Kralj"
><ShawnKralj@.discussions.microsoft.com> wrote:
>>Hi
>>I attempted to perform a simple mathematical operation (like dividing 10 by
>>4) in a text box of a Table or body of a report and I end up with an integer
>>result (10\4 gives me 2.0). I used convert method, CDbl method and I still
>>get the same result. I formated the text box as N5, C2 and other formats but
>>still the same result.
>>The original problem is that the same thing happens with a report parameter
>>when I try to use it in calculations but by troubleshooting the problem I
>>realized that it happens in any text box.
>>What am I doing wrong?
>>Thanks
>>Shawn

Saturday, February 25, 2012

Operand type clash: datetime is incompatible with text

I am getting this error on my insert statement, do I need to do something
for my datetime fields?
Here is the statement
INSERT INTO tblCalendar (
adjName, DispDueDate, ClaimNumber, Juris, HearingDate, HearingType,
ClaimantName, Location, HearingTime, HearingPart, WCB#, Counsel,
DateRecd)
VALUES (
@.Adjuster, @.newDispositionDueDate, @.ClaimNumber, @.Juris, @.newHearingDate,
@.HearingType, @.ClaimantName, @.HearingLocation, @.newHearingTime,
@.HearingPartRoom, @.WCBClaimNumber, @.AssignedCounsel, @.newNoticeReceived)You might want to check the datatypes of each column in tblCalendar table
and make sure they are compatible with the variables in the VALUES() clause.
If you have difficulty, post the exact CREATE TABLE statements and the
declared types and assigned values for each variables in the INSERT
statement.
Anith

Operand type clash error

Hello!

I am trying to update a DetailsView but I keep getting the error of Operand type clash: int is incompatible with text. I have double checked the stored procedure but I don't see any obvious offending parameters. ID is the only parameter that is an interger. I am not trying to update the ID at all, just for the filter purposes to update the records.

ALTER PROCEDURE [dbo].[proc_update]
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.BUILDING_SV float,
@.BUILDING_ADDS float,
@.BUILDING_DEL float,
@.BUILDING_TOTAL float,
@.ME_SV float,
@.ME_ADDS float,
@.ME_DEL float,
@.ME_TOTAL float,
@.PATTERNS_SV float,
@.PATTERNS_ADDS float,
@.PATTERNS_DEL float,
@.PATTERNS_TOTAL float,
@.SS_VALUE float,
@.SS_TOT float,
@.COMMENTS text,
@.OCCUPANCY_TYPE varchar(255),
@.REVISED_BY varchar(255),
@.DRAWINGS float,
@.INVENTORY float,
@.TOTAL float,
@.DIVISION varchar(255),
@.LOCATION varchar(255),
@.LOCATIONCODE varchar(255),
@.ADDRESS1 varchar(255),
@.ADDRESS2 varchar(255),
@.ADDRESS3 varchar(255),
@.ADDRESS4 varchar(255),
@.PROP_ID float,
@.POST_DATE smalldatetime,
@.ID int
AS
/* SET NOCOUNT ON */
UPDATE dbo.PROPERTY_VALUES
SET BUILDING_SV = @.BUILDING_SV, BUILDING_ADDS = @.BUILDING_ADDS, BUILDING_DEL = @.BUILDING_DEL, BUILDING_TOTAL = @.BUILDING_TOTAL, ME_SV = @.ME_SV, ME_ADDS = @.ME_ADDS, ME_DEL = @.ME_DEL, ME_TOTAL = @.ME_TOTAL, PATTERNS_SV = @.PATTERNS_SV, PATTERNS_ADDS = @.PATTERNS_ADDS, PATTERNS_DEL = @.PATTERNS_DEL, PATTERNS_TOTAL = @.PATTERNS_TOTAL, SS_VALUE = @.SS_VALUE, SS_TOT = @.SS_TOT, COMMENTS = @.COMMENTS,OCCUPANCY_TYPE = @.OCCUPANCY_TYPE, REVISED_BY = @.REVISED_BY, DRAWINGS = @.DRAWINGS, INVENTORY = @.INVENTORY, TOTAL = @.TOTAL, DIVISION = @.DIVISION, LOCATION = @.LOCATION, LOCATIONCODE = @.LOCATIONCODE, ADDRESS1 = @.ADDRESS1, ADDRESS2 = @.ADDRESS2, ADDRESS3 = @.ADDRESS3, ADDRESS4 = @.ADDRESS4, PROP_ID = @.PROP_ID, POST_DATE = @.POST_DATE
WHERE [ID] = @.ID
RETURN

Help!

Thanks.

I did more debugging. I discovered that the null values in the DetailsView are passing as "1" to the database. Anyone know how to debug this? Thanks.

|||

You probably have some default values set to 1, and "ConvertNullToDefaultValue" set to true (I forget the exact property name, but it's close).

|||

Thanks.

I don't recall setting the default values to 1. I am stumped why it is doing this. Is CONVERTNULLTODEFAULTVALUE a part of the codebehind or in the source page?

Thanks!

|||Please post the sqldatasource source (Everything between <asp:SqlDataSource and </asp:SqlDataSource>).|||

Here is it.

<asp:SqlDataSourceID="Update"runat="server"

SelectCommand="proc_update_select"UpdateCommand="proc_update"SelectCommandType="StoredProcedure"UpdateCommandType="StoredProcedure">

<UpdateParameters>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="BUILDING_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ME_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_SV"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_ADDS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_DEL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PATTERNS_TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_VALUE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="SS_TOT"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="COMMENTS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="OCCUPANCY_TYPE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="POST_DATE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="REVISED_BY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DRAWINGS"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="INVENTORY"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="TOTAL"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="DIVISION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATION"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="LOCATIONCODE"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS1"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS2"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS4"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ADDRESS3"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="PROP_ID"PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DetailsView1"Name="ID"PropertyName="SelectedValue"/>

</UpdateParameters>

</asp:SqlDataSource>

|||

Why is every update parameter a ControlParameter? The parameters that are getting their values from being databound should be a plain "Parameter". Also what is the selectedvalue of detailsview1 in your test? 1?

Please change the parameters from type ControlParameter to type Parameter. If the problem still persists, then launch your app, go to the detailsview, enter into edit mode (So the dropdowns show), then post the HTML that one of the dropdowns that is bound to a field that is submitting a 1 by clicksing Tools->View Source in IE, and copying everything between the <SELECT and </SELECT> tags.

|||

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

ProtectedSub Update_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles Update.Updating

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

Any Ideas?

EndSub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Conversion from type 'DBNull' to type 'String' is not valid.

Here is the code for the updated parameters

ProtectedSub Update_Updated(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles Update.Updated

For xAsInteger = 0To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

EndSub

|||

oops sorry for the last post...let me repost again.

I did what you said. It now no longer passing the null values as 1! Thank you!

However, it is not updating to the database even though I see the parameters updating. I am using the trace feature and the code below to trace the updating/updated parameters.

Here's the code for updating parameters

Protected Sub Update_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles Update.Updating

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

Here is the code for the updated parameters

Protected Sub Update_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles Update.Updated

For x As Integer = 0 To e.Command.Parameters.Count - 1

Trace.Write(e.Command.Parameters(x).ParameterName)

Trace.Write(CStr(e.Command.Parameters(x).Value))

Next

End Sub

I got an error for the updated parameters code

Conversion from type 'DBNull' to type 'String' is not valid.

Any Ideas?

|||

Help?

It won't update the fields that currently have null values...

Help again? :)

|||Are you getting an error? Is it updating the fields that don't have null values?|||

Yes to both.

I am still having the error that I posted above and the update is also updating one of the columns that didn't have a null value with a null value.

Strangely, enough the update works if there are null values but it will still catch the exception of this error Conversion from type 'DBNull' to type 'String' is not valid when I step into the updated parameters. I would like to fix that so there won't be any problems in the future even though it still works. The another problem is that the update function will update this column named BLDG_SV with a null value even though it had a value, previously.

Thanks!!

|||Is the column named BLDG_SV or BUILDING_SV?|||yes sorry it is BUILDING_SV, forgive me.

Monday, February 20, 2012

OPENXML Question

I have an text column in a table that was intended to store XML data about queries that users submitted in our database. The problem appears that the application inserting records into the table doesn't do a great job of formatting the XML and I am having a great deal of trouble getting workable results.

The XML data in the table looks like:
<request>
<name>LastName</name>
<oper>=</oper>
<value>Smith</value>
<name>FirstName</name>
<oper>=</oper>
<value>John</value>
<name>MiddleName</name>
<oper>=</oper>
<value>Q.</value>
</request>

Note the groups of three elements (name, oper, value). These always appear sequentially in the data and there are always a fixed number of elements. If an element has no data, then it contains the value 'null' (ie, <value>null</value>).

I want to get a result like:

Col1 Col2 Col3
======== ====== ========
LastName = Smith
FirstName = John
MiddleName = Q

When I execute OPENXML, I specify option 2 (element centric) and the following WITH clause:

WITH (name varchar(10),
oper varchar(10),
value varchar(50) )

I get a single row returned. I have also tried:

WITH (name varchar(10) '@.name',
oper varchar(10) '@.oper',
value varchar(50) '@.value')

I get a single row returned (with null values).

I've tried lots of other permutations, but nothing so far has worked. The only thing that has been modestly successful is to create and Edge Table (exclude the WITH clause), but that's really hard for me to work with. Another option is to insert a <line num="#">...<line> around each group of three elements (name, oper, value). That worked well, but it's a pain to implement (it means going back and updating all rows where the XML data exists and inserting the proper data.

I am by no means an XML guru, so if anyone has a suggestion, I'd love to hear it.

Regards,

hmscottI think you are struggling because you have assigned meaning to the order of elements in your source xml file. To paraphrase Brett: the order of elements in an xml file *should* have no meaning. Assigning meaning to the order of elements leads to fragile and inflexible code (dependent on element order to work correctly).

a better xml design would be something that groups each tuple, but (as you mention above) that means you have to change the xml.

For instance:

<request>
<var name="LastName" oper="=" value="Smith"/>
...
</request>

so that it's unambiguous which name/oper/value goes together.|||I think you are struggling because you have assigned meaning to the order of elements in your source xml file. To paraphrase Brett: the order of elements in an xml file *should* have no meaning. Assigning meaning to the order of elements leads to fragile and inflexible code (dependent on element order to work correctly).

I didn't assign meaning to the order of anything. This is legacy code that's been around since before my time (and which also means that the developer whose neck I wish to wring has long since departed).

I know what I would do to prevent the problem; what can I do to deal with the data as it is (about 1,000,000+ rows)?

Regards,

hmscott|||well, this works, but I'm not terribly proud of it. It's certainly fragile... :)

declare @.names table (id int identity(1,1), name varchar(10))
declare @.opers table (id int identity(1,1), oper varchar(10))
declare @.values table (id int identity(1,1), value varchar(10))

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<request>
<name>LastName</name>
<oper>=</oper>
<value>Smith</value>
<name>FirstName</name>
<oper>=</oper>
<value>John</value>
<name>MiddleName</name>
<oper>=</oper>
<value>Q.</value>
</request>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

insert into @.names (name) SELECT * FROM OPENXML (@.idoc, '/request/name',1) WITH (name varchar(10) '.')
insert into @.opers (oper) SELECT * FROM OPENXML (@.idoc, '/request/oper',1) WITH (oper varchar(10) '.')
insert into @.values (value) SELECT * FROM OPENXML (@.idoc, '/request/value',1) WITH (value varchar(10) '.')

EXEC sp_xml_removedocument @.idoc

select n.name, o.oper, v.value from
@.names n
join @.opers o on o.id=n.id
join @.values v on v.id=n.id|||well, this works, but I'm not terribly proud of it. It's certainly fragile... :)

Hey, thanks. I hadn't thought of trying it that way.
:beer:

That's what I get for beating my head against the wall for too long.

Regards,

hmscott|||glad to help. note that my idea falls down completely if there is a missing name,oper, or value element anywhere in your file, because then it would group the wrong elements together.

that's what I meant by it being fragile. :)|||glad to help. note that my idea falls down completely if there is a missing name,oper, or value element anywhere in your file, because then it would group the wrong elements together.

that's what I meant by it being fragile. :)

That's understood. Actually, the way the data is organized, I think I can throw this inside a cursor. It still will fail in the event of a missing element, but perhaps a bit of error checking will capture that.

Thanks again.

hmscott