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


ADVERTISEMENT

SQL 2012 :: Make AlwaysOn Over Normal Cluster

Nov 13, 2014

Can we make SQL 2012 Always ON over the normal SQL Cluster 2012.

View 3 Replies View Related

SQL 2012 :: How To Change String Key To Int - Normal Methods Not Working

Apr 13, 2014

did VARCHAR(20)
SELECT top 10 CAST(did AS INT) FROM table
Msg 245, Level 16, State 1, Line 3

Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.

SELECT CONVERT(INT, did) FROM table

Msg 245, Level 16, State 1, Line 5

Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.I found a SQL Function that removes all the alphabetic characters from the string and just leaves the integers.

View 4 Replies View Related

SQL 2012 :: Parsing Variables Between Stored Procedures

Oct 20, 2014

I've got a number of stored procedures that I have for reporting

All are of a similar starting format

For easier maintenance and to take away the need to change all of them if the methodology changes I want to split out shared code.

What I want to do is to take out the part that populates the @ID1 table into a separate stored proc which will be called from the report procs. The values from the shared proc will then be parsed back to the reporting proc.

I thought about using a function but I don't think it will be flexible enough as in certain cases I want to parse 2 or more IDs back into the final output.

I also don't want to make the code too complex so that it is relatively easy to read

CREATE PROC dbo.ReportM1 @ID INT AS
DECLARE
@ID1 TABLE (ID INT PRIMARY KEY, UNIQUE(ID))
IF @ID = 0
INSERT INTO @ID1

[Code] ....

The first question I have is: can i do it with a table variable when going between procs or do i need to build a real table if i want it to maintain the logic in 1 place.

May be worth bearing in mind that the end user who will be executing the proc will only have read + execute stored proc access permissions so dropping, updating or creating real tables is not an option. #Temp tables are possible but since am using table variables throughout would prefer to stick with them.

View 2 Replies View Related

SQL 2012 :: Parsing Large Varbinary Fields

Jun 9, 2015

I have a table with raw scientific test results in a single field, some of which are over 25Mb field. I need to parse into the field to find and aggregate selected values from the field.

Table structure is
CREATE TABLE [dbo].[Gxxx_Data](
[id] [uniqueidentifier] NOT NULL,
[Status] [nvarchar](50) NULL,
[GxxxItem_ID] [int] NULL,
[Stats_Data] [varbinary](max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[code]...

From which I need to parse and summarize the (Assembler) opcodes (MOV,CMPi, SHR etc...)I need to parse the large field [Stats_Data] to locate the target data.The internal result strings are delimited with Char(10), conservative counts are from 64k to over 100k lines in each record. Is there a way to parse the individual lines into another table (temp) that would be queried/regexed ?

View 9 Replies View Related

SQL 2012 :: Parsing Out Data String With Text Delimiters

Nov 5, 2014

I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.

For example,

[atc1.123/atc2.456/atc3.789/atc4.xyz/]

If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?

View 2 Replies View Related

SQL Server Naming Standards (or What Is Most Commonly Used)

Oct 29, 2007

Are there common naming standards for SQL tables and stored procedures? I'm creating a table for target audiences and was going to set it up like this:

Target_Audiences
Target_Audience_ID
Target_Audience


This table is really straight forward, but let me know if you would change anything. I want to use all of the most common naming standards throughout my database.

View 13 Replies View Related

SQL Server Best Practice Analyzer And T-SQL Coding Standards

Jul 23, 2005

Does anyone know much about this tool? Also, if anyone can point me toa TSQL coding standard, please let me know.-- Dave

View 2 Replies View Related

SQL Server 2014 :: Database And Its Objects Naming Standards

Mar 6, 2014

I am trying to establish the standards for naming convention in my new project. What are the best standards which worked.

View 9 Replies View Related

What Encryption (eg AES128) Standards Are Supported For SQL Server Compact 3.5 On WM5 And WM6?

Apr 15, 2008

I was told that AES 128/SHA1 is supported for SQL Server Compact 3.5. The problem is that I couldn't find any product literature from Microsoft that specifies exactly that and my client wants us to provide proof on that.

I hope to get the endorsement from the forum here, and it would be great if someone could point me to some Microsoft resources that specifies the support clearly.

View 1 Replies View Related

Server And Database Role Requests For Normal User

Jun 5, 2007

Hi,

I want to know how to assign a normal user to take backup,Restore and attach and detach the database in sqlserver2000.

Please can anyone help me to find out which server role or database role will suites my requirements.

Regards
Md S.Hassan

View 1 Replies View Related

SQL Server 2000 Produces A Shared DB Lock With A Use DB. Normal?

Nov 20, 2007

Hi Folks,

I'm not a SQL Server expert, but need to understand something. I'm currently have SQL Server 2000 SP4 installed locally on my machine for application development.

Using sp_lock, I noticed that there are lots of shared database locks. After some study, i produced this interesting script:

use Master
go
sp_lock
go
use Experience
go
sp_lock
go

which results in:

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
52 1 85575343 0 TAB IS GRANT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
52 13 0 0 DB S GRANT
52 1 85575343 0 TAB IS GRANT

My question is about the extra lock added by the "Use Experience statement". It lasts as long as the Experience DB connection is open

Question 1:
The way I read this is: the "use Experience" statement creates a shared database lock.
Am I reading this right?


Question 2:
I started looking into this because my application produces lots of locks that seem to live for 15-20 minutes. I have not noticed other adverse side effects of these locks. I have confirmed that nothing I have control over creates transactions, so I do not see why locks would be maintained.
Should I be concerned about these locks? Is this expected behavior?

Thanks Much in advance.

- D



View 3 Replies View Related

Move Data From Tables On Linked Server To Normal Database?

Nov 10, 2014

I'm trying to find a way to insert data from a TableA on ServerA into TableB on ServerB using SSIS in Visual Studio.

The specification I was given is basically

Insert INTO TableB AS (Select * from TableA WHERE NOT EXISTS on TableB).

I can't use a linked server unfortunately.

I wonder if it possible to move data from tables on a linked server to a "normal database"? What am I doing wrong?

View 4 Replies View Related

SQL Standards

Nov 25, 2004

I am writing an client application that will be connected to data source. However, I do not know what the data source will be (could be mySQL, SQL Server Express, MS Access, ODBC, etc). I would like to write my data access queries using a standard that will be accepted by various data sources. What is such standard and where can I read more about it?

View 1 Replies View Related

What Is SQL Standards?

Aug 22, 2000

Hi Everybody,

I have heard from people saying "Set SQL Standards for Object names and usage". What is that?. Can anyone explain me please.

tks in advance,
Srinivasan

View 1 Replies View Related

Database Standards HELP!

Sep 23, 2005

I would like to create a new database and follow some standard.  I am hoping that there is some ANSI documentation or Microsoft documentation on a NAMING standard when creating objects in the database.i.e  Table name "tblEmployees"     Column name "txtLastName"     Is there any GOOD documentation on creating a database using a PROVEN, and ACCEPTED standard?

View 2 Replies View Related

SQL Commenting Standards

Nov 25, 2006

Are there any sql commenting standards, for example some programming languages have commenting standards like Javadoc in Java, etc. Just wondering if there is any standard for TSQL comments?
thanks

View 2 Replies View Related

Naming Standards For SQL Objects

Sep 2, 1998

I was wondering if there are generally accpeted naming standards for SQL Server ojbects (tables, store procedeures, triggers, views etc.) that might be available somewhere on the WEB. I was also wondering if most DBA`s prefix the object names like "sp_" or suffix the object like "Customer_T"? Any opinions?

View 1 Replies View Related

Translate Sql Statement Into SQL-92 Standards

Jul 20, 2005

Can anyone help me translate this statement from using the legacyouter joins to the SQL-92 standards?Select CA.* From Customer C, Shipper S, Customer_Order CO,Cust_Address CA Where CA.Customer_ID =* CO.Customer_IDand CA.Addr_No =* isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No)and C.ID = CO.Customer_IDand (S.Shipped_Date between '1/1/2003' and '12/31/2003')Try as I may, I simply can't find a working left, right, or full outerjoin statement that would give me the same results as the abovestatement gives. I thought this was suppose to work but don't knowwhy it doesn't. Anybody care to try or perhaps tell me why thestatement below doesn't work:Select CA.* From Customer C, ((Customer_Order CO left outer joinCust_Address CA on CA.Customer_ID = CO.Customer_ID) left outer joinShipper S on CA.Addr_No =isnull(S.Ship_To_Addr_No,CO.Ship_To_Addr_No)) Where C.ID =CO.Customer_IDand (S.Shipped_Date between '1/1/2003' and '12/31/2003')Thanks,Tony

View 4 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

T-SQL (SS2K8) :: Database Coding Standards?

Jan 4, 2012

Is there a Microsoft published document for SQL database coding standards and guidelines/T-SQL coding standards available??

View 7 Replies View Related

XML Newbie: Sql 2005 Compliance To XML Standards

Nov 24, 2006

I was told that XML names must not start with the letters xml (or XML, or Xml, etc) But I was able to store such data in sql 2005. Any thoughts on this one?

TIA,



Barkingdog



View 1 Replies View Related

Output Data Bandwidth Standards?

Nov 1, 2006

All:

One of the new tests that we are running have to do with load testing an application with a constrained network pipe. I like this. One of my beefs has been related to stored procedures that return bloated result sets. This new set of tests potentially gives me some more amunition to use when I review stored procedures. A piece that I would like to produce as a result of this has to do with establishing an output bandwidth standard for our database servers. I have a few biased ideas but I would like to know if any of you have any similar pre-existing standards along this line. Any help?



Dave

View 1 Replies View Related

Standards For Custom Component Development

Jan 24, 2006

Hi,

Maybe I didn't search hard enough on BOL, but does Microsoft have a documented set of standards regarding custom component development for SSIS. Things like:

- extend this base class, implement this interface

- override these methods

- follow these naming conventions

- best practices

Thanks,

- Joel

View 3 Replies View Related

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 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 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

SSIS Package Development Standards/Workarounds

Apr 25, 2008

Having a difficult time setting up a development environment and a set of standards for SSIS package development.

First of all, you can't run the dataflow object "SQL Server Destination" in BIDS because BULKCOPY can only be run from the actual server. So how do you test/debug a package with this object in it?

Second of all, if you create an SSIS package on a developer computer in BIDS, and then import it into the SSIS package store on your development SQL server, you can't run the package from Management Studio on the developer PC. You get the error "DTS_E_PRODUCTLEVELTOLOW" when it tries to run any of the SSIS. Do I have to have SSIS installed on the developer client machine? How do I do that without installing a full server instance on each client machine (not to mention the licsense issues)?

Lastly, what protection level would you suggest using for production? We are having issues with ODBC connection passwords being decrypted and thus package steps failing in using "EncryptSensitiveWithUserKey". What exactly does this protection level do? Our network is physically very locked down, so we arent worried about SSIS package security too much, just looking for a way for them to work reliably without having to setup complicated security scenarios.

thanks for your time.

View 4 Replies View Related

IntelliSense And Standards Compliance Tools For SQL Coding

May 13, 2008

Visual Studio provides IntelliSense and targeted standards compliance code checkers that are extremely useful when writing code. A good example is for web pages targeted to XHTML 1.0 transitional versus strict versus XHTML 1.1.

Is there anything comparable for SQL coding in any of the Microsoft products whether Visual Studio or SQL Server Management Studio or any other development environment?

I'm looking for IntelliSense that can be targeted to one of three alternative configurations: (1) ANSI SQL-92 only, or (2) ANSI SQL-99 only, or alternatively, (3) T-SQL with proprietary Microsoft features/functions (ie, not ANSI compliant in the sense that it is no longer portable per ANSI criteria - it will break when ported due to presence of Microsoft proprietary features/functions).

If standards targeted IntelliSense is not available in any of the Microsoft products for SQL development, is there any third-party product that provides this capability?

View 2 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

Integration Services :: SSIS Standards And Documentation Template

Oct 21, 2015

I am sure this questions have been asked  but i was not able to find a useful information. 

1. I am looking for a SSIS Standards document or source. A document developer can use for developing ssis package. This should include how to name each container, task , and how to organize things. Basics of SSIS. Is there some kind of source where i can find this information . We are starting out to migrate from informatica to SSIS but before we do that we would like to put standards in place so all SSIS development is consistent.

2. SSIS project documentation template that we can use to document each project.  Is there anything out there that we can follow to document each of our project. 

View 2 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







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