|
I have a xml file on which i have to operate.
I am using following approach
Declare @c_cmd VARCHAR(255),@C_940IN_FILE varchar(100),@C_940IN_DIR varchar(100)<br />
<br />
Set @C_940IN_FILE ='S_outbound.xml'<br />
Set @C_940IN_DIR ='\\xceed\tech\'<br />
<br />
CREATE TABLE #TEMP_940 <br />
( <br />
ROWDATA varchar(8000) Null <br />
) <br />
<br />
SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + '''' <br />
+ ' WITH (FIELDTERMINATOR = ''><'')' <br />
PRINT @c_cmd <br />
EXEC(@c_cmd) <br />
----------------------- <br />
select * from #TEMP_940 <br />
<br />
Drop Table #TEMP_940
.
.
It gives me temp table with all nodes, like
<Case_Dimension><br />
<br />
<unit_of_measure>PK</unit_of_measure><br />
<br />
<quantity>6</quantity><br />
<br />
<unit_length/><br />
<br />
<unit_width/><br />
<br />
<unit_height/><br />
<br />
<dimension_measure>CM</dimension_measure><br />
<br />
</Case_Dimension>
Now i have to use a cursor for getting all the values of corresponding fields.
Is there any better way?
By which i get table which will create a temp table like, without using cursor
<br />
unit_of_measure quantity ... .... .... so on<br />
--------------- --------<br />
______PK_______ ___6____ ... ... ....<br />
regards
|
|
|
|
|
Hi
I have read the question a couple of times, but I'm still having trouble understanding your query. So, let's verify what we got;
Hum Dum wrote: I have a xml file on which i have to operate.
That's a physical file, located on the harddisk? I mean, it's not stored inside an SQL Server table or anything like that?
Hum Dum wrote: Now i have to use a cursor for getting all the values of corresponding fields.
To make sure I understood that correctly; you're first reading the columns from that file, and you'd be fetching it's values with a cursor?
Is it a requirement to use Sql to import the data, or would it be allowed to use C# or VB.NET? How will the import-process be started, does it get run by the server automatically, or does the user init the import?
Hum Dum wrote: Is there any better way?
There might be, depending on your requirements and restrictions. If you're allowed to program a solution, I'd rather suggest the XmlDocument[^]-class.
If it has to be done from Sql, I'd suggest converting the file from XML to (several?) CSV-files. Then again, if it's Sql Server, then you might even get away with creating a linked server to your file and SELECT INTO the destination table.
I are Troll
|
|
|
|
|
Eddy Vluggen wrote: How will the import-process be started, does it get run by the server automatically
SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + ''''
+ ' WITH (KEEPIDENTITY, FIELDTERMINATOR = ''><'', ROWTERMINATOR= ''<Product_Information>'')'
the above command will import xml data in to temp table #Temp_940 (see my original post).
I just need to execute this command and i have the data of XML.
Now i have to operate on this data row by row and insert it into another table. for that i have to use cursor.
Eddy Vluggen wrote: I'd rather suggest the XmlDocument[^]-class.
I know and used it also. when i suggest to use C# my PM says "NO".
"You must use SQL server."
So, its not my choice
|
|
|
|
|
Hum Dum wrote: for that i have to use cursor.
I presume you select statement will not do the job for you
Insert Tablename (columnames....)
select columnnames...
from #Temp_940
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi....
in which version of sql server i can get the below two option...
1) performance tools
2)database tuning advisor..
please send me the download site...
|
|
|
|
|
SQL Server 2005. But those features(which you have mentioned above) not exists in Express edition so you need buy SQL server 2005 enterprise edition. For more info click here[^]
thatraja |Chennai|India|
Brainbench certifications Down-votes are like kid's kisses don't reject it Do what you want quickly because the Doomsday on 2012
|
|
|
|
|
thanks .. i got in 2008 enterprise edition....
|
|
|
|
|
Hi,
In my server the MySQL database is not started. Because, the database was corrupted since the table size is large. So i resolved this issue and started the MySQL server by extending the innodb table space.
The below line is i added in my.cnf
innodb_data_file_path = ibdata1:100M:autoextend:max:500M;ibdata2:100M:autoextend
The MySQL is started and database and tables are there. The problem is when i select any table data, i got the error as "incorrect information in file frm".
How can we fix this issue?. Any idea pls.
Regards,
Periyasamy.R
|
|
|
|
|
restore your backup.
|
|
|
|
|
Hi Luc pattyn,
Thanks for your reply. Instead of restoring database Is there any other way by changing configuration in "my.cnf" file?.
Thanks,
Periyasamy.R
|
|
|
|
|
Hi
Can anyone advise/help me with the following
I am trying to
Select from table
where xfield HAS either (,),/,%, : in the first 3chars
For instance, the query should return the row
G1: darararararararar
and not
G12 easfsafsafsfsfsfsfsf
Thanks in advance
|
|
|
|
|
Can you explain more your question?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
You could try this:
select * from Table
where (xfield like '[,/%:]%' OR xfield like '_[,/%:]%' OR xfield like '__[,/%:]%')
|
|
|
|
|
How about
where instr(substr(xfield,1,3),'(') > 0
or instr(substr(xfield,1,3),')') > 0
or instr(substr(xfield,1,3),'/') > 0
or instr(substr(xfield,1,3),'%') > 0
or instr(substr(xfield,1,3),':') > 0
Can't remember if the % or : character need to be escaped.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
There is probably a really easy answer to this series of questions but I'm sort of a newbie in designing a database.
I know what a relational database is. I'm just not that familiar with designing one. My problem: Let's say I want to design a GUI interface for a kiosk for a library or bookstore. There could be several implementations and it could even be thought of as HTML links but the GUI looks nicer. The reason for this comparison is because I want the categories and sub categories to be dynamic, based on information stored in tables and obtained through queries.
I was thinking that I could have a table of main categories called "cats", and a table of sub categories called "sub cats", and of course a table for all of the books with a sub-category field. If a GUI is used, a blob for the image that represents the category or sub category can be assigned a field in the tables.
If I query the cats table and display all of the menu selections for the main categories and then when a main menu selection is made, say for "biographies", I can query for the second level menu items by looking for records in the "sub cat" table with a "Parent" field of "Biographies" which I imagine this could be only two records like "Biographies" and "Auto Biographies" and then drill down to the books table with a query to either of those two sub categories if a menu selection is made.
The problem I have understanding is a many-to-one relationship and how to implement it. I've discussed the first and 2nd level menus but if there is a third level menu and a book, item, or even a fourth level menu, can belong to more than one sub category, basically a sub cat or item with more than one parent, what do I do? Does this require multiple blank fields in one of the sub cat or book tables? Or, should this be implemented through using a primary key that encompasses many fields, or, even possibly should I have a field that declares what level menu the sub cat belongs in?
|
|
|
|
|
Put all categories in the same table and let the subcategories reference their parent categories like this:
CREATE TABLE Categories (
id NUMBER(8,0) NOT NULL,
parentid NUMBER(8,0) NULL,
name VARCHAR2(36) NOT NULL,
more stuff..
)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_pk PRIMARY KEY (id)
/
ALTER TABLE Categories ADD CONSTRAINT Categories_id_fk FOREIGN KEY (parentid) REFERENCES Categories (id)
Then select the top categories with: select id,name from categories where parentid is null
And a subcategory with: select id,name from categories where parentid = <ID>
The self referencing foreign key blocks accidental deletion of a category that has subcategories
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Yes, exactly.
|
|
|
|
|
Wow, I'm impressed. I had no idea it could be accomplished in so few of steps. Of course, I'm still trying to decode some of the "ADD CONSTRAINT" and REFERENCES language but I get the basic gist of what you're saying. I also had no idea that you could do the self referencing and thought, at minimum, it would require two tables. Very cool.
Thanks so much !!
|
|
|
|
|
I have a stored procedure defined thusly -- actual variable, database, and table names have been altered for NDA reasons:
CREATE PROCEDURE [dbo].[CountUniqueBirthdays]
@PersonID bigint
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CalendarDaysToSearch int
SET @CalendarDaysToSearch = 90
SELECT COUNT(DISTINCT o.Birthdays)
FROM
[MYDATABASE].[dbo].[PersonInfo] n,
[MYDATABASE].[dbo].[PersonInfo] o
WHERE
n.PersonID = @PersonID AND
--n.PersonID <> o.PersonID AND
-- all leads with...
n.KeyPerson=o.KeyPersonAND
-- within X days of current lead
o.CalendarDate between dateadd(dd, (-1 * @CalendarDaysToSearch ), n.CalendarDate ) and dateadd(dd, 0, n.CalendarDate )
END
For the sake of argument, assume this stored procedure is written correctly and is known to work. Now, I opened up VS2005 (my boss wants me to use it) and in the Data Sources window, I did the whole Add New Data Source/Data Source Configuration Wizard yada yada and added a TableAdapter to my DataSet for PersonInfo (not the real table name again).
I configured the TableAdapter to do a SELECT PersonId FROM PersonInfo and then also to call the Stored Procedure, CountUniqueBirthdays , above. ok, so i write the code to grab the records containing only the PersonID column and then I iterate over the column, passing each ID into the stored procedure one by one to check if the number of distinct persons with birthdays in a certain 90-day period is bigger than 1 -- AND DON'T ASK ME WHY DID I NOT JUST USE A CURSOR, CURSORS ARE SLOW AND WE HATE THEM, as below.
You know how you can open .xsd files in the Dataset Designer in VS2005? Well, I did, right-clicked the PersonInfo TableAdapter, clicked Add > New Query... and in the Add New Query Wizard I picked Existing stored procedure, and then configured CountUniqueBirthdays as the stored procedure to use.
<br />
try<br />
{<br />
Console.WriteLine("Querying the MYDATABASE.dbo.PersonInfo table...Please wait.");<br />
MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter adapter =<br />
new MYDATABASEDataSetTableAdapters.PersonInfoTableAdapter();<br />
MYDATABASEDataSet.PersonInfoDataTable table =<br />
adapter.GetData();<br />
Console.WriteLine("Finished getting data.");<br />
<br />
Console.WriteLine("Running the CountUniqueBirthdays stored procdure. Searching for return values > 1...");<br />
foreach (MYDATABASEDataSet.PersonInfoRow row in table.Rows)<br />
{<br />
long? personID = row.PersonId;<br />
if (!personID.HasValue)<br />
continue;<br />
<br />
Console.WriteLine("Checking output of CountUniqueBirthdays for PersonId = {0}...", leadID);<br />
<br />
long? value = adapter.CountUniqueBirthdays(leadID);<br />
if (value.HasValue) {<br />
if (value.Value > 1)<br />
{<br />
Console.WriteLine("Value > 1 found for return from LQSCountVendor190Days...for <br />
leadId = {0}",leadID);<br />
break;<br />
}<br />
}<br />
}<br />
}<br />
catch (Exception e)<br />
{<br />
Console.WriteLine(e.Message);<br />
return;<br />
}
The output from the console app is:
<br />
Querying the MYDATABASE.dbo.PersonInfo table...Please wait.<br />
Finished getting data.<br />
Running the CountUniqueBirthdays stored procdure. Searching for return values > 1...<br />
Checking output of CountUniqueBirthdays for PersonId = 8752702...<br />
Specified cast is not valid.
Why? I followed what the IntelliSense had me do when I was typing in the stored procedure call! I don't get it, can somebody please help a newbie at Database programming?
Brian
|
|
|
|
|
The return type for CountUniqueBirthdays may not be long (or long? for that matter). Have you tried [int]? Or just receive it into an [object] variable and use Watch to see what it contains? i.e.
object value = adapter.CountUniqueBirthdays(leadID);
|
|
|
|
|
Thank you for your help. Yes, actually the IntelliSense was off and doing a Build Solution fixed it so that the IntelliSense told me the return type was object , and I was off and running!
Brian
Sincerely Yours,
Brian Hart
|
|
|
|
|
I have changed the data type of the currencyID and countryID from NUMBER to INTEGER as number is not supported by dotnet.
When I run this query the data_type is still number.
SELECT C.Table_Name ,
C.Column_ID ,
C.Column_Name ,
C.Data_Type,
C.char_length
FROM ALL_TAB_COLUMNS C
WHERE C.TABLE_NAME = 'CURRENCY'
ORDER BY C.column_ID
Any idea how to refresh the ALL_TAB_COLUMNS view or can there be another reason for the change not showing.
Also - any suggestions as to the best Oracle support site. No it's not CP, we are very MS oriented.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In Oracle, Integer is simply Number(38). So if you create a column as an Integer it's converted to number(38)
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
When I look at the table in Schema view (toad) I see integer, if I script the table out I get integer, I'm betting if I used a parameter with a data type it would be integer as well. I have now found that if I create a new table the columns do not show up in the ALL_TAB_COLUMN view, seems it might be a permissions thing.
As NUMBER is not supported by dot net this seems very strange! Do all the orm tools convert number to integer/decimal.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I haven't worked in Toad so I can't say.
But an implicit conversion in dotnet is generally making a widening conversion to decimal unless the scale of the number is specifically 0 then it converts to integer.
If you want a different conversion you have to specify it in the parameters of the dataadapter/datareader.
Here's [^]more info on types.
And here http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055 is some info on the subject from Tom Kyte, who is an institution in the Oracle World
<edit> fixed link, kind of</edit>
"When did ignorance become a point of view" - Dilbert
modified on Monday, November 8, 2010 5:07 AM
|
|
|
|
|