Tag Info

New answers tagged

2

I can't think of an easy way to do it other than fixing up the strings, eg DECLARE @yourString VARCHAR(MAX) = '%3CRoot%3E%3CTMN%3E2014-10-23+10%3A00%3C%2FTMN%3E%3C%2FRoot%3E' SET @yourString = REPLACE ( @yourString, '%3c', '<' ) -- Replace 'less than' symbol SET @yourString = REPLACE ( @yourString, '%3e', '>' ) -- Replace 'greater than' symbol ...


1

You can do some limited adjustments to the schema with ALTER XML SCHEMA COLLECTION. Here's a simple example: USE tempdb GO IF OBJECT_ID('dbo.users') IS NOT NULL DROP TABLE dbo.users GO IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test') DROP XML SCHEMA COLLECTION xsd_test GO -- Create a simple schema CREATE XML SCHEMA ...


0

If you want the longest substring that means there is no other which is longer. A NOT EXISTS predicate will give this. select <whatever> from <your table> as aa where <predicates> and not exists ( select 1 from <your table> as bb where <predicates> and len(bb.SomeColumn) > len(aa.SomeColumn) ); ...


0

The only viable solution I could find was to write a function: FUNCTION get_noun_inflection( p_noun text, ... params ... ) -- ... body, declare, variable, etc. SELECT CASE -- ... conditions ... THEN trim( TRAILING ni.label_singular FROM p_noun ) || ni.label_plural -- Noun in singular form (no pluralization) ELSE p_noun ...


3

What is wrong with the (maybe too obvious?): select * from noun n, noun_inflection ni where n.label = 'handlebar' and n.label ilike '%'||ni.label_singular order by char_length(ni.label_singular) DESC limit 1;


2

As you are using the XML datatype, you must be using SQL Server 2005 or above. Therefore you can use the methods of the XML datatype (eg .nodes, .value, .query) to read the XML. For such a small piece of XML performance is likely to be the same or better than OPENXML, eg DECLARE @xml XML SET @xml = '<NewDataSet> <Table1> ...



Top 50 recent answers are included