CODEWITHSUNDEEP
xmlt-sqlsql-server-2005xquery
slipsec
slipsec

Reputation: 3062

Split string in SQL Server's xquery

Is there a way to do something like the (unsupported) xquery tokenize in SQL Server?

I have:

<WITS:foo>
 <WITS:bars>
  <bar details="name=theName;value=theValue" other="false" stuff="true">
  </bar>
 </WITS:bars>
 <WITS:bars notThis="oneIdontwant">
  <bar></bar>
 </WITS:bars>
</WITS:foo>

and I want to pull out theName. The closest I have been able to come is to get name=theName;value=theValue via 

SELECT
 x.query('
  distinct-values(
   for $name in data(//WITS:foo/WITS:bars[not (@notThis)]/WITS:bar[@details="name"))
   return $name
  )'
)
FROM (SELECT CAST(xmlColumn AS XML) x FROM blah Where blahblah) y

So far it looks like I'm out of luck since this is all running on SQL Server 2005, and from msdn it seems like the only thing there is to work with strings is the four functions listed (no index-of, or other options).

Right now I'm parsing after the fact, but I was wondering if there are any other ways I could get this done server side.

Upvotes: 1

Views: 1424

Answers (2)

jr pineda
jr pineda

Reputation: 186

Provided that you were able to get name=theName;value=theValue. You can use TSQL XML to split the items into rows. With the Query below.

declare @xml xml
SET @xml = N'<root><r>' + replace('name=theName;value=theValue',';','</r><r>') + '</r></root>'
SELECT t.value('.','NVARCHAR(20)') AS [delimited items] FROM @xml.nodes('//root/r') AS a(t)

From here, you can redo the same process to split name=theName.

Upvotes: 2

gotqn
gotqn

Reputation: 43666

You need to use the following string operations:

SUBSTRING - Returns part of a character, binary, text, or image expression.

CHARINDEX - Returns the starting position of the specified expression in a character string.

Note, the links refer to SQL Server 2005 functions.

I believe using CHARINDEX and SUBSTRING will be the easiest solution. An other way is to use regular expressions but they are not well supported even in newest versions and you will need to use CLR procedure - too complicated for such an sample task.

Upvotes: 0

Related Questions