Hot answers tagged

13

You can shred the XML and rebuild it again using XQuery. declare @X xml = ' <Root attr1="val1" attr2="val2"> <Elements> <Element Code="1" Value="aaa" ExtraData="extra" /> <Element Code="2" Value="" ExtraData="extra" /> <Element Code="3" ExtraData="extra" /> </Elements> <ExtraData> ...


12

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'')])) ...


11

An easy way to do this is to use the nodes method to get right to the address attribute and check for your @ sign. The problem with the way you're looking now is that it's only checking that any email address has an @ in it. Parsing the XML nodes out lets you check individual emails for it. DECLARE @x XML = '<xml> <customer name="Max"><...


11

This might work for you: select @MyXml.value('/R[1]', 'varchar(50)') It picks up all text() elements from the first R and below. If you just want all text() you can do select @MyXml.value('.', 'varchar(50)') If you want the values for I and J separate do this instead. select @MyXml.query('/R/I/text()').value('.', 'varchar(50)'), @MyXml.query('/...


11

You'll need to declare the namespaces in the modify function. Something like this: DECLARE @xml xml = N'<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <CentralData> <SMData> <CentralSDItem> <ControlData>1234</ControlData> </CentralSDItem> &...


9

This will Work Perfectly® WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p ) SELECT qp.plan_name, qp.query_xml.value('count(//p:RelOp/p:NestedLoops/@WithUnorderedPrefetch)', 'int') AS operator_count FROM dbo.query_plans AS qp; If you prefer, you could use WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/...


9

If you have the XML in table you should use UPDATE instead of SET and there you can put the namespace declaration outside the XML_DML expression using WITH XMLNAMESPACES. with xmlnamespaces(default 'DataService/1.0.0.0') update DataImpTable set serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem ...


8

You could do that using the sql:column function and a Common Table Expression (CTE), something like this: CREATE TABLE #tmp ( col2 XML ) INSERT INTO #tmp ( col2 ) VALUES ( '<Root> <Row> <Rowid>trim this value </Rowid> </Row> </Root>' ) SELECT 'before' s, * FROM #tmp ;WITH cte AS ( SELECT col2, RTRIM( ...


8

The three problems with your XQuery that I can see are (and none of this has anything to do with it being Typed XML): You are not specifying the correct path to the /root node. It should be: SELECT t.c.value('(.)[1]','varchar(50)') as type from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/transaction') as t(c) XML is case-sensitive so you need ...


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


8

Specifying the text() node before the predicate will be more efficient than having text() in the predicate. select * from #ColumnSet as C where AllValues.exist('*/text()[. = "POSITIVE"]') = 1 Query plan with text in the predicate AllValues.exist('*[text() = "POSITIVE"]') = 1


8

Does something like this satisfy the resultset? Table & Data CREATE TABLE #TestingIdea ( Id int PRIMARY KEY IDENTITY (1,1), PostId int NULL, Tag nvarchar (MAX) NULL ) INSERT INTO #TestingIdea(PostId,Tag) VALUES(1,'<mysql><innodb><myisam>') GO Query SELECT PostId, RIGHT(value,len(value)-1) as SplitTag FROM #TestingIdea CROSS APPLY ...


8

You may try following SELECT @xml.query(' element a { for $node in /a/*[local-name() != "d"] return $node }'); But it may not be suitable if your real XML is more complex and you have to exclude node at a deeper level of nesting. Speaking honestly, I don't think that XQuery is the good tool for such sort of tasks. In essence we ...


8

I don't think there is anything you can do to optimize the XML_DML statements. But you can do something about the exists check in the where clauses. It is better to fully traverse down to the value you are checking before the predicate. So instead of .exist('/Root/Row[date=''2007-05-10'']') you could do .exist('/Root/Row/date/text()[.=''2007-05-10'']'). ...


7

Depending on your actual XML structure you could consider using a loop like this: DECLARE @xml XML SELECT @xml = '<R> <I>A</I> <I>B</I> <I>C</I> <J>X</J> <J>Y</J> <J>Z</J> </R>' SELECT Tbl.Col.query('for $i in I return $i').value('.', 'nvarchar(max)'), ...


6

I have not found a simple way to just modify the replace value of statement to work with anonymous simple type definitions. Simple repro of what you have: drop xml schema collection dbo.SimpleTypeTest; go create xml schema collection dbo.SimpleTypeTest as N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="root"> ...


5

You can also use the methods of the XML datatype (eg modify) and some XQuery to modify the xml, eg DECLARE @x XML = '<Root attr1="val1" attr2="val2"> <Elements> <Element Code="1" Value="aaa" ExtraData="extra" /> <Element Code="2" Value="bbb" ExtraData="extra" /> <Element Code="3" Value="ccc" ExtraData="...


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 '<Z><A><b>1&...


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

If you have the value in a variable you can use sql:variable() in the predicate for the delete. declare @XML xml = ' <Root> <Sub> <Record> <Guid>aslkjflaksjflkasjfkljsd</Guid> </Record> <Record> <Guid>opqiwuerl;kasdlfkjawop</Guid> </Record> </Sub> </Root>...


5

Here is a rewrite of what you already have. No shredding necessary on root node. Specify the text() node for the parameter name and parameter value. In my limited tests that made it significantly faster. I look forward to see what if any performance gain you see on your side. select T.TestId, T.TestData.value('(/root/@name)[1]', 'varchar(max)') as ...


5

Use predicates with name tests like this instead of "simple" node steps: /*[lower-case(local-name(.)) = 'z']/*[lower-case(local-name(.)) = 'a']


5

I had another look at this and can reproduce your issue. Try adding OPTION ( MAXDOP 1 ) to your query. In my test rig with a 300MB file this ran in 1 min 42 seconds. The unhinted version ran for 30 minutes at 100% CPU before I killed it. You could also have a look at OPENXML. People often say it's faster with large XML files and it appears to be in this ...


5

Here's an XPath solution, though I don't know how efficient it will be. SELECT * FROM #ColumnSet WHERE AllValues.exist('//*[text() = "POSITIVE"]') = 1


4

I found the answer myself after reading through loads of documentation from Oracle. The answer comes in two parts. First, my original question can be solved by using XMLTable: SELECT x.ID AS "MEASUREMENT_ID", x.GUID AS "MEASUREMENT_GUID", a.COLUMN_VALUE AS "MESSAGE" FROM SPERF_XML_RAW x, XMLTable('/PerfInfo/EventLog/...


4

To know what performance you will have you have to test on your data. I obviously can't do that so I made up my own xml data to test the two queries you have in this question. Create a table with 5000 rows containing an XML document of 9475 characters in 415 nodes: create table T ( ID int identity primary key, XMLCol xml not null ) declare @X xml = ( ...


4

Specify float when fetching the value from the XML. select @XML.value('(//Balance)[1]', 'float'); To change the value in a XML column with decimals included you need to first extract the value as a float and write it back as an numeric with the appropriate precision and scale. Something like this. update T set XMLCol.modify('replace value of (//Balance/...


4

You were oh so close. You were definitely on the right track with using the .query() function and using the contains XQuery function. What you got wrong was: Putting the = False outside of the [...] (meaning, it was not part of the contains() expression) Using the word False instead of the function false() Not specifying the parent node by adding /.. to the ...


4

UPDATE: I have updated the code, as well as the input and output XML in the example query below to reflect the latest requirement, stated in a comment on @Mikael's fine answer, which is: to not create Value element if @Value is empty or doesn't exist While a single expression can correctly match this new variation, there does not appear to be a way to ...


4

You can do it in xQuery with a FLWOR Statement and Iteration (XQuery) using cast on each value returned. SELECT @xml.value('sum(for $val in //val return $val cast as xs:int?)','INT') A query like that could make you think that SQL Server will do some time consuming looping operations but if you have a look at the query plan you will see that is not the ...


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