SQL Server 2008 :: Parsing Out XML On Same Level

Aug 20, 2015

I am trying to parse out

<Discharge_x0020_Time>
<time>
<Hour>11</Hour>
<Minute>:00</Minute>
<AM_x002F_PM>AM</AM_x002F_PM>
</time>
</Discharge_x0020_Time>

Into Hours, minutes, and ampm

I use

Select...
,DISCHARGEHOUR.value('(./Discharge_x0020_Time/time/Hour)[1]', 'varchar(10)') AS [hour]
,DISCHARGEMINUTES.value('(./Discharge_x0020_Time/time/Hour:minute)[1]', 'varchar(10)') AS [Minutes]
,DISCHARGEAMPM.value('(./Discharge_x0020_Time/time/Hour/minute/AM_x002F_PM)[1]', 'varchar(10)') AS [ampm]

FROM ...
CROSS APPLY data.nodes('/Data') a(DISCHARGEHOUR)
CROSS APPLY data.nodes('/Data') b(DISCHARGEMINUTES)
CROSS APPLY data.nodes('/Data') b(DISCHARGEAMPM)

But minutes AND AMPM come up as NULL I assume I am setting up something wrong with the level on minutes AND AMPM. Also, can I disregard the ":" in the minutes.

View 1 Replies


ADVERTISEMENT

SQL Server 2008 :: XML Query Parsing

Mar 9, 2015

I want to take this XML and put it into a table with CustomerId and MatchingSetId. With this SQL, each MatchingSetId gets assigned to each CustomerId instead of retaining the relationships in the XML.

declare @myXML XML = '<CustomerMatchings>
<CustomerRecord CustomerId="10600">
<MatchingSetId>11</MatchingSetId>
<MatchingSetId>13</MatchingSetId>
<MatchingSetId>18</MatchingSetId>
<MatchingSetId>23</MatchingSetId>

[code]....

View 3 Replies View Related

SQL Server 2008 :: Parsing Unstructured CSV File?

Oct 1, 2015

I have a CSV file with roughly 6 million rows. The file is unstructured; that is, some rows have 5 fields, others have 15, and there are as many 50 fields in one row.

I am using bulk insert to read the entire file into a table in database, with each row being a database record. With that, I have one column that contains a row of comma delimited fields. All fields are character string and I want to find a quick way of parsing each row and placing each comma-delimited value in a column. For example:

CREATE TABLE MyTable
(
CSVString varchar(1000),
C1 varchar(20),
C2 varchar(20),
...
C50 varchar(20),
)

Column CSVString contains the a CSV row (I don't know how many filelds (no. of commas + 1) in the row, but if the row contains 10 fields, I need to populate columns C1-C10. If the row has 15 fields, I populate columns C1-C15.

How can I do this in a very efficient way? I tried CTE but performance was not very good.

View 8 Replies View Related

SQL Server 2008 :: Parsing Data To Select Certain Values From XML

Mar 13, 2015

I have results that are XML data and I am trying to figure out how to parse the data to select certain values from the xml.

example
<InformationRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" teamid="TEAM003341507" playerid="PL341508" gameid="G000000852" playertype="Starter" FolderName="Test" CurrentYear="2015" Ultimateid="P00000688505" xmlns="http://schemas.sports.com/Messages/Stats" />

I would like to write a statement that just pulls the game id G000000852. So just the id right of gameid=.

Not really sure where to start. Table is GAME, and column is XMLDATA.

View 0 Replies View Related

SQL Server 2008 :: Text String Parsing To Apply Operators To Datasets?

Aug 7, 2015

I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.

An example would be having a formula stored of [a] + [b] - [c]

and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)

select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id

The formula can change though, maybe things like:

[a] + [b] + [c] + [d]
[a] + [b]

The developer before me wrote something SQL-based where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.

View 5 Replies View Related

SQL Server 2008 :: Changing Isolation Level

Apr 30, 2015

We are using sql 2008r2 standard edition.One of our Production database is using default isolation Readcommitted.The transactions also using read committed. But we want change isolation level to read comitted snapshot isolation and test it to avoid deadlocks.

Is it possible to set in the transaction level for some queries or do we need to change entire database isolation level by using alter database "ALTER DATABASE AdventureWorks2008R2 SET READ_COMMITTED_SNAPSHOT ON"

View 8 Replies View Related

SQL 2005 - .Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 - Error Parsing Statements

Nov 2, 2006

Hi,

Does anyone else have this error message pop up in SSMS when you try to parse sql statements:

.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

There was a thread back in March 2006 that mentioned this error, but the posted resolution was to install SP1.  I have SP1 installed but I still get the error.

I only receive the error when I'm parsing statements, if I run the statement it's fine.

 

Thanks

Matt

 

View 8 Replies View Related

SQL Server 2008 :: Database Level Selection Settings?

Apr 23, 2015

I have following table structure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FAS_LEDGER](
[TID] [INT] IDENTITY(1,1) NOT NULL,
[TDATE] [DATETIME] NOT NULL,

[code].....

in this table I have 1571182 rows

the problem is someone did some changes in this LEDGER table

I can insert new rows in this table but when I try to fetch latest rows on the basis of following query then I did not get the latest row.

means

following query gives all the rows of this table

SELECT * FROM dbo.FAS_LEDGER ORDER BY TID DESC

and when I try to filter Master_code = '02-07-01-008-0001' and apply oder by TDATE I do not get latest rows

SELECT * FROM dbo.FAS_LEDGER WHERE MASTER_CODE = '02-07-01-008-0001'
ORDER BY TDATE DESC

View 4 Replies View Related

SQL Server 2008 :: Script To List All Database Level Permissions In A Database?

Aug 4, 2015

I found this script online..

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

but the results are this: 2 columns - User and Permission

User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT

IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?

View 7 Replies View Related

SQL Express 2008 And Row Level Compression

Dec 7, 2007

Will the 2008 edition of SQL Server Express contain the new row level compression features? Is it available in the current CTP for us to try out?

Thanks

View 1 Replies View Related

Reporting Services :: SSRS 2008 - Report Builder Download To Domain Level Clients Not Working

Feb 5, 2010

I have created a new SSRS Server and done all of the basic security setup for the site but I am having trouble with client machine access...At first I had an issue with being able to access report manager from any client and ended up having to shut off windows authentication on the client IE settings to get it to work.  Now I am trying to run Report Builder from a client machine and it will not download. The client machine is on the same domain and my user id is set as an administrator within SSRS and as a local admin on the 2008 server. 

I had the thought that without windows auth in the browser maybe it can't permit me access to the application but if I turn it on I can't get to it to download it.  I have set Full Control rights to the folder as well. Most of the info out there is for 2005 and that uses IIS to host the pages where as 2008 does not. Here is the details of the error:

PLATFORM VERSION INFO Windows    : 6.1.7600.0 (Win32NT) Common Language Runtime  : 2.0.50727.4927 System.Deployment.dll   : 2.0.50727.4927 (NetFXspW7.050727-4900) mscorwks.dll    : 2.0.50727.4927 (NetFXspW7.050727-4900) dfdll.dll    : 2.0.50727.4927 (NetFXspW7.050727-4900) dfshim.dll    : 2.0.50727.4927 (NetFXspW7.050727-4900)

[URL] ....

View 2 Replies View Related

SQL Server Syntax Parsing

Feb 23, 2008

Hi All,

In SQL Studio, there is a really handy option to parse your query prior to running it (that little green tick button next to Execute).

I dont suppose there is a similar thing that can be used on the command line at all is there??

Cheers
-
Karym6

View 2 Replies View Related

Data Parsing In SQL Server Views

Jul 23, 2005

If column1 in SQL Server column is text: 19980701What is the syntax in the select statement to convert it to a datelike: 07/01/1998Thanks for any helpRbollinger

View 1 Replies View Related

SQL Server 2012 :: Parsing XML That Does Not Quite Fit Normal XML Standards

Jan 21, 2015

I have an application that stores xml data in an unusal manor. Basically a SQL Key column and an XML string.The XML string is not really standard XML, but it is what it is, and I'm stuck with it. It is in the format;

<row key="Value.01" xml:space="preserve"><c1>FirstName</c1><c2>LastName</c2><c3>10 Street Address, City ST 012345-1234</c3><c4>5</c4><c5>50</c5><c6>500</c6></row>

I am able to pull values out via
SELECT
p.value('(./c1)[1]', 'VARCHAR(8000)') AS c1,
p.value('(./c2)[1]', 'VARCHAR(8000)') AS c2
FROM dbo.UserXMLTable
CROSS APPLY XMLRECORD.nodes('/row') t(p)
where p.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'

However I've been struggling with selecting row with a LIKE clause. Something like ;

SELECT *
FROM dbo.F_UserXMLTable
where XMLRECORD.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'

I have tried a number of permutations of XML syntax but so far have been stumpled.

Please note "<row key="Value.01" xml:space="preserve">" has a <SP> in the name 'row key' .

View 3 Replies View Related

Can I Run SQL Server 2008 - Reporting Services CTP Until Microsoft Sells SQL 2008

Mar 31, 2008

There are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.


Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?


Jim

View 1 Replies View Related

Seeking Advice: SQL Server On Win 2008 Virtual Server Or Just Win 2008?

Apr 23, 2008



Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.

Any pointers to documentation or any advice?

Thanks,

A Brown

View 1 Replies View Related

Column Level Or Database Level Encryption/decryption....

Jan 16, 2008

I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256  algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance

View 1 Replies View Related

High Level &&amp; Detail Level Design Documents

Nov 19, 2007



Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance

View 1 Replies View Related

Problem With Windows Server 2008 And SQL 2008 Express

Feb 25, 2008

Hello!
Recently, I set up server with Windows Web Server 2008 RC1, SQL 2008 Express beta, .NET 3.5, IIS 7.
I'm running ASP.NET web application with SQL database. Everything works fine until the first application state on the server expires. After that, any postback that starts a new application state on the server and connects to the database, results in the following error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Is this a bug that will be fixed in release of Windows / SQL or am I doing something wrong?
Many thanks for help,
Jan

View 1 Replies View Related

Microsoft SQL Server 2008 CTP, February 2008

Mar 7, 2008




Hi all,



New to this so please be patient and please help.



I have developed an SQL 2005 Express command line option install for our company which has been working seamlessly for the last 18 months.

http://msdn2.microsoft.com/en-us/library/ms144259.aspx

I downloaded the €œMicrosoft SQL Server 2008 Express CTP, February 2008€? from http://www.microsoft.com/downloads/details.aspx?FamilyId=749BD760-F404-4D45-9AC0-D7F1B3ED1053&displaylang=en



I simply replaced the 2005 file €œSQLEXPR.EXE€? with the 2008 file €œ€?, recompiled the installation and tested only for it to fail. I than read the 2008 books online and noted the change in command line options.

http://www.microsoft.com/downloads/details.aspx?familyid=19DB0B42-A5B2-456F-9C5C-F295CDD58D7A&displaylang=en

http://msdn2.microsoft.com/en-us/library/ms144259(SQL.100).aspx

I then changed the command line to suit the Microsoft 2008 books online, recompiled the installation and tested only for it to fail once more.



Interestingly I tested the install from the default GUI and at the point of adding the €œsa€? login credentials it fails to allow the installation to proceed. Strangely by selecting the windows authentication credentials, €œnext€? than €œback€? it now allows me to add the €œsa€? login credentials and continues to install correctly as required.



I hope I have explained this clearly enough.


1. Is this a bug in the €œMicrosoft SQL Server 2008 CTP, February 2008€? installation?
2. If so is this causing the command line install options to fail?
3. How do I obtain a version of €œMicrosoft SQL Server 2008 Express€? that will work installing from the command line?





Thanks in advance.


View 1 Replies View Related

Server: MSg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server Does Not Exist Or Access Denied

Jun 6, 2007

Hi,

When I am trying to access SQL Server 2000 database from another machine i got this error

Server: MSg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied

but I could access the database on same server and in that server i could access other databases in different server.



View 6 Replies View Related

Transactions = Table Level Or Row Level ?

Nov 29, 2006

When you utilize transactions in ADO.NET are the locks put on the entire TABLE used or at the row level?
For instance if you do a SELECT within a transaction if you only pull 5 rows out of a 1000 row table can you just make it lock the rows that have been pulled?  It seems like it locks the entire table?
 
Thanks

View 6 Replies View Related

What Is Low Level And High Level Locking

May 3, 2007

Hi, Can anybody please explain me, what is low level and high level locking in SQL Server 2005 database.
Also what is the name of process which converts low level locking into high level locking and vise versa.
-Sanjeev

View 2 Replies View Related

Statement Level Vs. Row Level Triggers

Jul 20, 2005

Hi..I'd very much appreciate it if someone would tell me how to translatea statement level trigger written in Oracle to its equivalent (if there isone)in MS SQL Server. Ditto for a row level trigger.If this is an old topic, I apologize. I'm very much a newbie to SQL Server.Regards,Allan M. Hart

View 2 Replies View Related

Query Analyzer Error Unable To Connect Server Local Msg17, Level 16,state 1/ODBC SQL Server Driver [DBNETLIB]SQL Server Does Not

Oct 20, 2007

I am getteing
need help
Query analyzer error Unable to connect server local Msg17, level 16,state 1
ODBC SQL server driver [DBNETLIB]SQL server does not exist

View 6 Replies View Related

SQL Server Admin 2014 :: Server Level Trigger To Log Activity And Rollback After Logging Information

Sep 8, 2015

I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:

create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end

I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.

View 8 Replies View Related

SQL Server 2000 Server: Msg 18456, Level 14, State 1, Line 1 Login Failed For User 'sa'.

Feb 28, 2007

As an 'SA' I thought that you have permission to do anything on the server. Running an update command from Query Analyzer is throwing this error:

SQL Server 2000 SP3

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.


The table is in the dbo schema.

Has anyone seen this error before?

View 12 Replies View Related

Server: Msg 207, Level 16,

Aug 21, 2004

In a Stored Procedure, I created a table in code:

CREATE TABLE TEMP ( ...)

, then ALTER TABLE TEMP ADD int1 int, varchar2 varchar(25)

I inserted some rows to the above table. Everything looks good.

Here's what puzzled me:

SELECT * FROM TEMP shows all the rows inserted.

But

SELECT int1, varchar2 FROM TEMP caused this error:

Server: Msg 207, Level 16, State 3, Line 5
Invalid column name 'int2'.
Server: Msg 207, Level 16, State 1, Line 5
Invalid column name 'varchar2'.

Thanks for any help.

View 1 Replies View Related

A Transport-level Error Has Occurred When Receiving Results From The Server. - After Installing New Instance Of SQL Server 2005 W/NO App Changes

Sep 27, 2006

We've devoted a resource to this today, but I have to believe it's something easy that we're overlooking.  The scneario is that we have a production Web application that until last weekend had a SQL 2000 back end.  This weekend we installed a new instance of SQL 2005 and everything works (we tested in a sandbox environment, but someone must not have load tested enough) and never saw these exceptions.   So, after the upgrade we now receive 100's of thexe SQL excptions per day:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)Does anyone know what we've overlooked that's causing this issue?Thanks for any help! 

View 2 Replies View Related

SQL Server Admin 2014 :: Audit All Server DDL Level Events?

Mar 6, 2015

I have been tasked with auditing all DDL and selected DML events on a production server and logging them to a table. My solution is to use CDC for the DML and a Server-Level trigger for the DDL. Because there should never but much DDL activity on the server (except when performing update tasks) I don't need to worry about the trigger consuming too many resources.

My question is this: Is there any single specification such as DDL_LEVEL_EVENTS that can capture all DDL activity or do I need to specify each and every DDL action in the trigger?

View 1 Replies View Related

SQL Server 2000 - Server: Msg 9002, Level 17, State 6, Line 1

Jul 20, 2005

I have a stored procedure that takes 18 hours to complete. SometimesI get the below error message when I run the stored procedure.'Server : Msg 9002, Level 17, State 6, Line 1 The log file fordatabase 'Customer' is full. Back up the transaction log for thedatabase to free up some log space.'.I checked my log file and it is not full. And I don't know why I amgetting this message. Any help is appreciated.Thanks,Anjula

View 2 Replies View Related

How Do I Do Row Level Locking In The Sql Server?

Apr 6, 2001

View 2 Replies View Related

Server: Msg 229, Level 14, State 5???

Jun 23, 2000

Server: Msg 229, Level 14, State 5, Procedure sp_monitor, Line 167
EXECUTE permission denied on object 'sp_monitor', database 'master', owner 'dbo'.


How do I fix this? which roles, or permission is that? Is there a
easier way to find out solutions for these problems, as books online
does not seem to provide much help.

Thanks,

Vijay

View 2 Replies View Related







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