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


9

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

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

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

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


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


7

You can extract the node text with XML methods as long as the value is well-formed XML. If the XHTML value is a parameter or variable: DECLARE @XHTML xml = N'<SPAN style="WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; FLOAT: none; COLOR: rgb(0,0,0); TEXT-ALIGN: left; FONT: 10px verdana; DISPLAY: inline !important; LETTER-SPACING: ...


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

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

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's an XPath solution, though I don't know how efficient it will be. SELECT * FROM #ColumnSet WHERE AllValues.exist('//*[text() = "POSITIVE"]') = 1


5

I'm probably missing the point of your question but here is what I would do to get the result you are looking for. with xmlnamespaces('ModelDescriptive' as dsc) select M.X.value('@ref', 'varchar(max)') as ref, M.X.value('@baseRef', 'varchar(max)') as baseRef, M.X.value('@type', 'varchar(max)') as type, M.X.value('@productionGroup', '...


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

Somewhat simpler than the FLWOR version. select * from myTable where yourXML.exist('(Z/A,z/a)[(b,B)=1]') = 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/...


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