OpenXML Elements Case Sensitivity Problem

Mar 19, 2007

Hi All - I am using OpenXML to insert data into SQL Server 2005. All works okay except for the fact that in my XML I have an attribute tag and elements as follows.
<Location>
    <Place City = "LA" State = "CA">
    <Place city = "NY" State = "NY">
    <Place City = "St. Louis" state = "MO">
</Location>
 
If you notice in the above code,  the 2nd city NY has element tag "city" in lower case and others are "City" with a Upper Case "C" same for state in case of MO which is lower case and others are Upper Cases..
Now I understand that XML is case sensitive. What should I do if I have XML files in the above format with inconsistent element tags? Because if I run the below code.. using OpenXML...

Insert Into TableX
Select *
FROM OPENXML (@DocHandle, '/Location/Place', 1)
                             WITH (City varchar(255) '@City', State varchar(255) '@State') ox
 
Then in the above scenario it won't insert values for city "NY" and state "MO" since both have lower case elements tags and in my OpenXML I have specified as @City and @State with Upper Case "C" and "S" respectively.
 
Now in such a scenario what can one do? Any thoughts is to how I can also grab value for lower case "city" and "state" in the above XML.
 
Any help would me much appreciated.
 
Thanks a billion.

Regards

- web

View 2 Replies


ADVERTISEMENT

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.

Sep 6, 2007

I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem  here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.

View 2 Replies View Related

Case Sensitivity

May 3, 2007

I am wondering if T SQL in SQL Server 2005 is case sensitive.

I running am running a query in a stored procedure whchi compares a passed in value with that in a field in the database, as such, is their a need to do thisSELECT * FROM table WHERE UPPER(column_name) = UPPER(@var) or will this return the same resultsSELECT * FROM table WHERE column_name = @var

View 2 Replies View Related

Case Sensitivity

Oct 3, 2000

Does anyone know if it is possible to disable case sensitivity without having to reinstall SQL Server?

View 2 Replies View Related

Case Sensitivity

Apr 16, 2006

When I do
select * from mytable where somecolumn='a'

I get rows for data with the value 'a' and rows for data with value 'A'

How to control cases sensitivity?
Thanks.

View 5 Replies View Related

Case Sensitivity

May 6, 2007

Hello !

I have two questions:

1) is it possible to make case sensetive researche in SQL-Server?
like : SELECT * FROM mytable WHERE myColumn = "This" [and not "this"]

2) is it possible to make case sensetive unique indexes? Allowing to add "This" and "this" in the same indexed column?

Thanks a lot for any feedback!

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 3 Replies View Related

Case Sensitivity

Jul 7, 2007

Hi I have two questions about case sensitivity;
1. Do most people write their queries with UPPERCASE keywords or is that only for the textbooks. Personally I find it a hassle, My little finger aches because it is always on and off the SHIFT key
2. I noticed that when I did the following query
-----------------------------
use AdventureWorks
go
Select * employee
-----------------------------
I get an 'invalid object' message
But if I use;
--------------------------
use AdventureWorks
go
Select * Employee
--------------------
It works. SO obviously the query is case sensitive.
Did this use to be the case with SQL 2000. I don't seem to remember having to be careful about case.

THANKS

View 5 Replies View Related

Case Sensitivity

Nov 13, 2006

Is SQL server defaults to case insensitive? I am sure there are ways toinstall case sensitive SQL server instance but coming from Sybase(which is always case sensitive) case insensitivity is something new tome (it requires coding change etc).Besides, is there any option I can set to turn on case sensitivity or Iam stuck with it? (I can not reinstall SQL server).Thanks.

View 1 Replies View Related

Case Sensitivity

Apr 3, 2008

I scripted a function to a sql file, transferred the file to a different machine and when I run it it fails. It turns out that it thinks @result does not exist because I delcared @Result. Changing it to uppercase R resolves the problem. I usually keep my case sensitivity but missed this one. This is the first time I've ever seen sql care about the casing though! Is it a setting?

View 8 Replies View Related

SQL Case Sensitivity In SQL2005

May 15, 2007

Hello all,
I've come across a bit of an issue, I'm testing software delivered by another company that includes an SQL2005 database. I've rolled back their live database on my developer database, but my server has a binary collation set as default server collation.
Because of this (or so I think) all variable/table/etc names in any SQL code are case sensitive, even when called in the context of a database with a case insensitive collation. As an example, when I run the following code on my developer computer I get an error, on their live server I don't.DECLARE @number INT
SET @Number = 1

Because of this I am getting alot of errors from their code, since their entire application has differing capitalization usage from page to page. Is there any way around this or do I need to reinstall the SQL Server on my computer to set a different collation?
Any help would be appreciated, thanks in advance.

View 2 Replies View Related

Case Sensitivity In SQL -- Ignore

Mar 15, 2007

Hi,I believe my SQL server was configured as Case sensitivity. I have anumber of stored procedures which were moved from a non-Casesensitivity SQL server. Because of the Case sensitivity, I have to doa lot of editing in those stored procedures. Is there a quick way toavoid the editing?Something like ignoring the case in one statement?Thanks in advance, your advice will be greatly appreciated.

View 2 Replies View Related

How To Remove Case Sensitivity In Database

Mar 2, 2008

Hi All,
How to remove case sensitity from database like table names,column names etc.
 If we type either select * from AUTHORS" or "select * from authors" should result the same value.
Abdul
 
 

View 1 Replies View Related

How To Change Case Sensitivity Of A Server?

Mar 4, 2002

I require to change the case sensitivity of a server. If I run rebuildm (changing the sensitivity to the desired value) will that not mean that I won't be able to reattach the databases?

Can anyone advise please.

thanks

Derek

View 1 Replies View Related

Sort Order/case-sensitivity

Aug 30, 2000

I have a case-insensitive sort order on my SQL7, NT4, SP1,2 installation. However, there are times when I want to do case-sensitive search ciriteria.

Is there something like the pseudo code:
SET SORT ORDER CASE-SENSITIVE ON
SELECT * FROM table WHERE name = 'Black'
SET SORT ORDER CASE-SENSITIVE OFF

..that doesn't return 'BLACK'

thanks in advance for your time
BL

View 1 Replies View Related

Enable And Disable Case Sensitivity

Feb 20, 2004

Hi,

I use a SQL Server 2000.

Is there a chance to change the case sensitivity option for a running instance of sql server?

I know, during the install process, i can activate the case sensitivity. But can I change this option on an already installed server (to case sensitiv or back)?

Thanks

View 4 Replies View Related

Change Case Sensitivity After Database Set Up

Jul 20, 2005

I have a SQL Server database hosted with a web hosting company. TheSQL Server was clearly set up to be case sensitive, however, I wantthis particular database to be case-insensitive.I have searched high and low, the best suggestion I can find is toreinstall SQL Server and select case-insensitive. But since this isthe web host's SQL, that isn't an option here.With default language I can use the sp_defaultlanguage to change toBritish settings (for example). Is there something similar I can useto make just this database case insensitive?--Popular uprising?http://www.blairfacedlies.org/statue.htmcaptain(underscore)flack(squirlything)hotmail(you know what)com

View 2 Replies View Related

Sort Order And Case Sensitivity

Jul 20, 2005

I have a query which filters records containing uppercase andLowercase i.e.Smith and SMITH, Henderson and HENDERSON etc.Is there a way that I can filter only those records that contain thefirst uppercase letter and the remaining lowercase letters for myquery i.e. Smith , HENDERSON etc.Thanks

View 6 Replies View Related

Latin1_General_BIN Case Sensitivity When Inserting Into Tables

Feb 27, 2008



So I have been unfortunate enough to inherit a whole estate of sql servers running a collation of Latin1_General_BIN.
I have a batch of maintenance and monitoring scripts that I want to implement on these servers. However I seem to have problems compiling the stored procs as the case of the column names varies throughout the script and table definitions.


Ok I know I could recode them, but is there way to overide the case sensitivity in insert and update statements so they dont have to be recoded?

View 1 Replies View Related

Transact SQL :: How To Set Case Sensitivity Of A Column In Design Time

Aug 3, 2015

With Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?

N.B.: Using SQL Server 2005.

View 22 Replies View Related

Case Sensitivity...Sellect * From Table1 Or Table1... Which One?

Feb 1, 2008

Hi everybodyI'm a newbie to SQL-Server 2005 so please excuse me for my silly questionsRecently i wrote a simple web-Page in which i select data from a table and then bind them to a GridView on the formevery thing was simple as follows:
        SqlConnection Con = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True");        Con.Open();
        SqlDataAdapter da = new SqlDataAdapter("SELECT [ID], [Name], [Date] FROM [Table1] ORDER BY [Date]", Con);        DataSet ds = new DataSet();        da.Fill(ds,"MyTable");
        GridView1.DataSource = ds.Tables["MyTable"];        GridView1.DataBind();
But I have a question , when i change the [Table1] to [table1] (With lowercase T) , i receive the following errormessage:
 Invalid object name 'table1'.
why is that? could anyone help me? even i tried to test this issue in [Microsoft Management Studio] and i got thesame result . realy what's the reason? before and with sql server 2000 i didn't have these issue.
Thanks in advance.Regards.
 

View 5 Replies View Related

Accent And Kanatype Sensitivity

Jan 14, 2008

This is kind of a big generic question, if anyone could even just point me to an article or white paper on this it would be helpful, but I can't find an answer. I do work for a international company that currently does all in english, but is looking to support more languages.


What exactly do the Accent and Kanatype Sensitivity tags in RDL refer to, and how would/when should they be used?

Thanks for any help

View 1 Replies View Related

Sensitivity To The Distribution Of The Binary Dependent Variable

May 23, 2007

Hi all,



I have a data mining model that has a binary dependent variable (let's say 'Yes' and 'No'). I noticed that the model is very sensitive to the distribution of the binary dependent variable. I could not come up with an explaination. I know the model is not over/under fits and multicollinearity is not an issue. And I have enough data size to train the model.

I would expect some sensitivity but not to this extend:











Yes % in training data
Yes % in the prediction results


with DT
with LR
with NN

30.0%
6.0%
11.0%
14.0%

35.0%
12.0%
15.0%
24.0%

37.5%
20.0%
23.0%
27.0%

40.0%
28.0%
33.0%
36.0%

50.0%
43.0%
52.0%
61.0%



DT - Decision Trees

LR - Logistic Regression

NN - Neural Networks



And to make things more interesting, although the precision (% of correct 'Yes's ), true positive ratio and false positive ratio is changing; the overall accuracy (and therefore the score in the mining accuracy chart) remains almost same.



If anyone has an idea it would be highly appreciated.



Thanks in advance, Afsin









View 3 Replies View Related

Transact SQL :: XML Elements To Row

Jul 14, 2015

extracting data from XML

DECLARE @xml XML = '<event_result>
<custom_elems>
<custom_elem>
<name>test_01</name>
<value>25</value>
</custom_elem>

[code]...

View 3 Replies View Related

OpenXML

Nov 19, 2007

Hi

I've run into a problem using openxml and would greatly appreciate any help or direction!

The Xml document I am working with contains the following excerpt:


<Address>
<UKAddress>
<no>24</no>
<Line>A</Line>
<Line>B</Line>
<PostCode>GIR 0AA</PostCode>
</UKAddress>
</Address>

My problem is this: There can be a variable number of line elements (between 2 and 5), and in addition each element tag is identical to the others, i.e. Line.

I am trying to populate a SQL Server 2000 table (which contains the columns AddressLine1, AddressLine2, etc.) using OpenXML

Here's the code for my sproc....

.
...
.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, 'root/data/', 2)
WITH (Number int '//no',
// address line detail here
Postcode varchar(10) '//PostCode')
.
...
.

I'm ok with OpenXML/sp_xml_preparedocument syntax and that, but I can't think how to handle unknown multiple address lines with the same tag name!

ANY HELP GREATLY RECEIVED!!!! :)

View 2 Replies View Related

OPENXML Vs BCP

Oct 3, 2005

Hi,My application writes data into sql server.Currently it converts data into XML (an in memory XML string) and writeusing OPENXML.I want to know if i write it to a csv file and use BCP, then will it befaster then OPENXML. (i feel, writing to a csv will create IO operation thatwill slow down the process).Thanks

View 1 Replies View Related

Using OPENXML

Jul 20, 2005

I have a SP set up to take an input param and then fire the followingsp_xml_preparedocumentOPENXML (With a select statement)sp_xml_removedocumentThis works fine with the XML syntax that microsoft provides in itsdocumentation ...<ROOT><Customer><CustomerID>VINET</CustomerID><ContactName>Paul Henriot</ContactName><Order OrderID="10248" CustomerID="VINET" EmployeeID="5"OrderDate="1996-07-04T00:00:00"><OrderDetail ProductID="11" Quantity="12"/><OrderDetail ProductID="42" Quantity="10"/></Order></Customer><Customer><CustomerID>LILAS</CustomerID><ContactName>Carlos Gonzlez</ContactName><Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"OrderDate="1996-08-16T00:00:00"><OrderDetail ProductID="72" Quantity="3"/></Order></Customer></ROOT>The problem I am having is parsing XML data given to me in this format<?xml version="1.0" standalone="yes"?><Active_x0020_Directory_x0020_Users><Groups_Duz_x0060_mp><whenCreated>2/13/2004 7:13:21 PM</whenCreated><whenChanged>2/13/2004 7:13:21 PM</whenChanged><sAMAccountname>!WGSyEnBuCoBuDoCG</sAMAccountname><cn>!WGSyEnBuCoBuDoCG</cn><groupType>-2147483646</groupType></Groups_Duz_x0060_mp><Groups_Duz_x0060_mp><whenCreated>2/13/2004 7:12:04 PM</whenCreated><whenChanged>2/13/2004 7:12:04 PM</whenChanged><sAMAccountname>#11SeanTest</sAMAccountname><cn>#11SeanTest</cn><groupType>8</groupType></Groups_Duz_x0060_mp></Active_x0020_Directory_x0020_Users>As you can see the elements are defined differently, I have an XSLfile as follows<?xml version="1.0" standalone="yes"?><xs:schema id="Active_x0020_Directory_x0020_Users" xmlns=""xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element name="Active_x0020_Directory_x0020_Users"msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Groups_Duz_x0060_mp"><xs:complexType><xs:sequence><xs:element name="whenCreated" type="xs:string"minOccurs="0" /><xs:element name="whenChanged" type="xs:string"minOccurs="0" /><xs:element name="sAMAccountname" type="xs:string"minOccurs="0" /><xs:element name="cn" type="xs:string" minOccurs="0" /><xs:element name="groupType" type="xs:string"minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema>So now I am wondering how I tell SQL Server to use the new file formatfor XML?Thanks in advance for your help.

View 2 Replies View Related

DTS Package Elements At Run Time Through VB6

Jan 31, 2004

How can i configure DTS package elements at run time through VB6..?
Like Specifying the Source,Destination etc

View 1 Replies View Related

Drop Down List With Many Elements

May 31, 2007

Hello,

I am using SSRS 2005 SP2 and I need to allow my users to select the products they want in the report.
I created a data set to get the list of the products and then used it in a multivalue parameter to generate a drop down list.
My problem is that the list is too long (more than 2000 elements) and thus, it takes 10 sec to collapse the drop down list after tickinging the products.

Does anyone have an miraculous solution?
Thanks in advance for your answers.

Zoz

View 8 Replies View Related

Display Only Time Elements

Jan 18, 2008

This one I hope is easy, but I've been working late and it seems to be aluding me. I have a field called time_in which is a char(6) field type. This field holds time elements such at 10:57

My query below is substracting one hour from Time_In and returning it as Hold_Time. I only want to return the time element without the date portion or the milliseconds, etc.



Code Block
select substring(cu.time_in, 1,5) as time_in,
DATEADD(hour, -1, substring(cu.time_in, 1,5)) as hold_time
from reports.careunits_hold_hours cu



[Current Result Set]

Time_In hold_time
10:57 1900-01-01 09:57:00.000

[Desired Result Set]

Time_In hold_time
10:57 09:57

View 3 Replies View Related

Reuse Of Report Elements

Jun 5, 2007

I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.



We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.

What is the best way to approach this?



My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.



My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination



If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!

View 2 Replies View Related

Why OPENXML Returns Only First Row?

Aug 11, 2006

 Whats wrong with the following code,its compiling but returning only first memberid. i want all memberids to be returned
DECLARE @memberList VARCHAR(4000),@hDoc INT
SET @memberList = '<MemberList><MemberID>7136</MemberID><MemberID>7137</MemberID><MemberID>7138</MemberID></MemberList>'exec sp_xml_preparedocument @hDoc OUTPUT, @memberListSELECT MemberID FROM OPENXML (@hdoc, 'MemberList', 2)         WITH (MemberID BIGINT)
EXEC sp_xml_removedocument @hDoc

View 1 Replies View Related

SQL OPENXML: Best Approach

Feb 27, 2008

Hello everyone.
I am new to.Net and here is what I have to do.
 I needto update a SQL table with data coming from a XML file.  I have seen some Microsoft documentation on this (the nice SQL statement that updates and inserts in the same stored procedure) but I don'tknow what is the best approach for passing my XML file to the stored procedure.  The XML contains about 12 000 records, kind of phonebook info (name, email, phone).
 What would be the best approach to do this?  What objects should I use?
 Thanks a million,
Ben
 

View 2 Replies View Related

XML - Openxml With SQL-Server

Feb 13, 2004

Im trying to update a row that all collumns are required (can not be null). Let say if i want to remove "No2" from the insert command but by default i want to make sure that in my collumn "No2" in the table of my database i have zero into it.

=================
with tbl_xmltest
=================
My problem is on this line above. When i remove in my XML column No2 i got an error.. "'OpenXML'. This column cannot be NUL". Is there a way to use my sql-command

================================
declare @data nvarchar(4000)
declare @handle int

--select top 1 * from tbl_xmltest for xml auto
set @data = '<tbl_x005F_xmltest No1="1111" No2="234" No3="3333"/>'


exec sp_xml_preparedocument @handle output, @data

begin tran
select * from tbl_xmltest

insert into tbl_xmltest select * from openxml(@handle, '//tbl_x005F_xmltest') with tbl_xmltest

select * from tbl_xmltest
rollback
EXEC sp_xml_removedocument @handle

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved