Tag Info

Hot answers tagged

12

Sounds like what you need is sparse columns and filtered indexes and go with option 1. These are fully supported and documented features for exactly this scenario. The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row ...


12

The reason for the performance difference lies in how scalar expressions are handled in the execution engine. In this case, the expression of interest is: [Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0) This expression label is defined by a Compute Scalar operator (node 11 in the serial plan, node 13 in the parallel plan). Compute Scalar ...


11

Use an external process that does the HTTP work and the inserts into the database. I explicitly advise against using SQLCLR for this. Hijacking precious SQL Server workers for the boring job of waiting for HTTP results will one day impact your server severely. but the size of the XML can be quite variable (esp given that a binary would pass it to the ...


10

I bookmarked Phil Factor's blog post Normalisation and 'Anima notitia copia' today as it neatly summarises the case for and against normalising certain types of data. Run the following query on a SQL instance and see if you agree. SELECT * FROM sys.syslanguages SQL enables you to create relational databases. However, even if it smells bad, it is ...


9

There are a lot of facets to this, so it really depends. Does adding the XML typed columns to the same table change the speed of querying that table? More data potentially could be read (I/O), but when you say speed of querying, if you mean finding rows based on columns outside of the XML, this should usually be based on a index and so the xml should ...


9

The exist() Method (xml Data Type) returns a bit. 1 if at least one node is found and 0 if no nodes are found (empty result set). To get the rows where neither ABC or XYZ exist you just have to compare the result of exist with 0. [myxmlcolumn].exist('for $x in /Root/Row where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) ...


8

When working with XML in SQL Server you use the xml Data Type Methods and when shredding XML documents you typicly use the nodes() and value() methods. The XML you have here also include a number of namespaces so you have to specify the ones you need using WITH XMLNAMESPACES (Transact-SQL). The XML is quite complex so without knowing how you want the data ...


8

There is no statistics generated on XML columns. The estimates is guessed based on the expressions used when querying the XML. Using this table: create table T(XMLCol xml not null) insert into T values('<root><item value = "1" /></root>') And this rather simple XML query: select X.N.value('@value', 'int') from T cross apply ...


8

There are two points of confusion here. The first is that you seem to think 'NULL' and NULL are the same; they're not. The former is a string literal, the latter merely signifies the absence of a value. The second is that you can use NULL in equality comparisons; and you cannot. Try this: SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END; Result: ---- 0 ...


6

The second statement is creating an object table. It is almost never the case that you really want to use an object table. That was something that was introduced in the 8i time frame when Oracle was making the database object oriented. While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something ...


6

Give this a try: SELECT t1.UserName AS "Account/UserName", t1.Hash AS "Account/Hash", t2.Phone AS "Personal/Phone", t2.City AS "Personal/City" FROM table1 t1 INNER JOIN table2 t2 ON t1.rid = t2.rid FOR XML PATH ('UserDetails'); I have added a second code sample in response to your comment. I have no idea if this will work, ...


6

That is the code from my article originally posted here: http://www.sqlservercentral.com/articles/deadlock/65658/ If you read the comments you will find a couple of alternatives that don't have the performance problems that you are experiencing, one using a modification of that original query, and the other using a variable to hold the XML before ...


5

A nullable column takes no space if variable length in SQL Server. The fact of being NULL is stored in the NULL bitmap. You can index it if required with filtered indexes so you ignore NULL columns. Adds complexity when you consider point 1. Don't. Hard to search, parse etc: you will regret this later It also depends on size: will this be char(1000) for a ...


5

It depends, as XML is not an RDBMS. SQLite allows the querying of relations. XML (and JSON) allow for the navigation of hierarchies. Completely different ontological mappings of reality. If you need an RDBMS backend, SQLite is for you. If you need record storage without any sophisticated query requirements, XML may work, depending on your language.


5

Without an ORDER BY clause, the order the values are returned in will be unreliable, as they can be influenced by indexes and the data pages the individual rows are stored on. If you require the data to be in a particular order, use the ORDER BY clause.


5

You have not specified the length of @id. Using only varchar will in this case give you a length of one so the content of @id will be x. Your first query checks for nodes that contain x and that matches all rows. The second query checks for nodes that has x as value and no row has that. Change the declaration of @id to DECLARE @id varchar(3) and everything ...


5

Your queries are not equivalent. The first will give you rows where both node names and values match where your second query only check the node values. SQL Fiddle The extra cost for the second query comes from using a table valued function to shred the XML. First query: Second query:


5

You can easily test the performance yourself. Create a regular table that you can test your queries on. create table myTable ( yourXML XML ) Add a couple of rows that will give you a match. INSERT INTO myTable SELECT '<z><a><b>1</b><c>2</c></a></z>' INSERT INTO myTable SELECT ...


4

If Queries against the XML will happen by sql server xml capabilities, then use XML type to store a xml to avoid casting And keep in mind, that XML type may be stored little bit slower due to xml validation, but underlying type of XML is ordinary varbinary(max)


4

You can import your data into a staging table then update the product reference data from that. The product table can hold your 'available' flag. Unfortunately you will have to import the XML data into SQL Server to load it, but SSIS has an XML reader, so you can use that to do the imports.


4

I use sp_OACreate extensively to do the sort of thing you are doing. I suspect that your problem may be that the size of the XML data you are receiving exceeds 8000 characters. (i.e. SQL may not be returning any data when you exceed 8000 characters). sp_OAGetProperty and other extended stored procedures cannot pass varchar(MAX) parameters As a ...


4

There is a comparable issue logged on connect for the version you're having a problem with - An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1. Using SQL2008, when shredding XML using the nodes query, the performance is fine when just SELECTing. However, when you want to INSERT the data to a table/temptable/tablevariable the ...


4

You could try using MySQL's mysqldump utility. There is an option to make mysqldump export in XML [redwards@lw-lts-155 ~]$ mysqldump --help | grep xml -X, --xml Dump a database as well formed XML. In your case, you could dump the table mydb.mytable like this: mysqldump --xml mydb mytable > mytable.xml EXAMPLE For the given table ...


4

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000). The mistake you did with your extra path expressions is that you forgot Stuff in the path. Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found ...


4

You can use X-Query for that: SQL Fiddle MS SQL Server 2008 Schema Setup: CREATE PROCEDURE dbo.ParseXML @XML XML AS BEGIN SELECT Book.value('@id','NVARCHAR(100)') AS id, Book.value('author[1]','NVARCHAR(100)') AS author, Book.value('title[1]','NVARCHAR(100)') AS title, Book.value('price[1]','DECIMAL(10,4)') AS price, ...


3

As far as Oracle databases are concerned, the answer is you can't. All data in a database is stored in tables, even meta-data. Data can be stored in queues, but those are just a different way of using tables. XML files can be stored outside of a database, but that wouldn't meet your "in the database" requirement. Moving beyond your stated question, JPEG ...


3

This parse node based XML. It's different to read attributes but it isn't as common I had this lying around as a demo with 3 slightly different XPath queries DECLARE @foo XML SELECT @foo = N' <harrys> <harry> <fish>0.015000000000</fish> <bicycle>2008-10-31T00:00:00+01:00</bicycle> ...


3

BOL says: syntax: nodes (XQuery) as Table(Column) Here is simple example: DECLARE @x xml ; SET @x='<Root> <row id="1"><name>Larry</name><oflw>some text</oflw></row> <row id="2"><name>moe</name></row> <row id="3" /> </Root>'; SELECT T.c.query('.') AS result FROM ...


3

The whole "inserting multiple records with XML" has pretty much been superseded by table parameters in SQL Server 2008. And mentioned in Erland Erland Sommarskog's Arrays and Lists in SQL Server 2008: the definitive article on this subject. .net has better XML handling than the SQLXML implementation too.


3

Similar to this answer to a previous question, and if you don't want the restrictions of pg_read_file() (in short: pg_read_file can't read files outside the database directory, and reads text in the current session's character encoding). This function works for any path, but needs to be created as superuser: create or replace function ...



Only top voted, non community-wiki answers of a minimum length are eligible