New answers tagged xml
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 ...
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) ); ...
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 ...
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;
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> ...
Thanks so much guys for all you effort. This actually does the job by changing int to nvarchar(10) from the script that was posted by Mikael Eriksson: select cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())','nvarchar(10)')SiteID
If your data is not that clean, then XQuery function fn:number is actually quite good at cleaning it for you. Try this simple demo: IF OBJECT_ID('tempdb..#yourTable') IS NOT NULL DROP TABLE #yourTable CREATE TABLE #yourTable ( rowId INT IDENTITY PRIMARY KEY, msg VARCHAR(8000) ) INSERT INTO #yourTable ( msg ) VALUES ( ...
You should use the value() method of the XML data type. If your XML structure looks like this: <DLR> <siteID>12345</siteID> </DLR> Your query would be select msg.value('(/DLR/siteID/text())', 'int') as DOMAIN from dbo.YourTable But it does however not look like you have an XML column at all since msg like '<DLR%' is ...
If MS SQL Server is what you're using, it supports XPath query within the SELECT statement. See here for an example and here's an SO question. This will allow you to extract the numerals from within the xml irrespective of how many digits it contains. You can then CONVERT() it to int or whatever as needs be.
Top 50 recent answers are included