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
Nov 13, 2014
Can we make SQL 2012 Always ON over the normal SQL Cluster 2012.
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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