CODEWITHSUNDEEP
sqlsql-servert-sqlxml-parsingsql-server-2008-r2
Question3CPO
Question3CPO

Reputation: 1202

TSQL: Parse By Word, Not Specific Character

I've looked for TSQL code to parse out by word, not character, as I have strings of XML in a table that need to be removed. Unfortunately, they don't follow a precise formula, meaning there can be 5 to 20 XML strings, ie (in this case it's only 3):

<code>
<codeblue>Answer A</codeblue>
<codered>Blue Twenty</codered>
<codeyellow>WARNING: only push if active</codeyellow>
</code>

Between each are sometimes text or numbers, and I want each of these to be stored in columns:

CodeBlue                       |   CodeRed                         | CodeYellow
<codeblue>Answer A</codeblue>  |   <codered>Blue Twenty</codered>  | <codeyellow>WARNING: only push if active</codeyellow>

Of course, some columns will be NULL, as there may be anywhere from 5 to 20 XML strings. Because of this, CHARINDEX won't properly extract, as I need to store the XML in column and thus '<' or '>' is not a correct identifier because the length can change depending on the text or numbers between the code.

My question is can I parse text by word or phrase, for instance, parse out <codeblue>Answer B</codeblue> out of a column of 8000 characters by searching for the keyword codeblue?

Upvotes: 0

Views: 181

Answers (2)

djangojazz
djangojazz

Reputation: 13272

I think you need to learn more about XML query methods. This is relatively easy with a combination of the 'query' method which hunts down an element structure. Then a 'value' which can then return an attribute or text value in the element.

declare @xml xml = '<code>
<codeblue>Answer A</codeblue>
<codered>Blue Twenty</codered>
<codeyellow>WARNING: only push if active</codeyellow>
</code>'

select 
    @Xml.query('//code/codeblue').value('.', 'varchar(max)') as codeBlue
,   @Xml.query('//code/codered').value('.', 'varchar(max)') as codeRed
,   @Xml.query('//code/codeyellow').value('.', 'varchar(max)') as codeYellow

References for XML methods: http://msdn.microsoft.com/en-us/library/ms190798.aspx

Upvotes: 1

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

SQL Server has built-in XQuery functionality which allows you to query XML in exactly the way you are asking. Here is an example:

create table #t (x xml);

insert #t
select
'<code>
<codeblue>Answer A</codeblue>
<codebrown>ignore this</codebrown>
<codered>Blue Twenty</codered>
<codegreen>ignore this too</codegreen>
<codeyellow>WARNING: only push if active</codeyellow>
</code>';

select  Codes.n.value('codeblue[1]','varchar(max)') As CodeBlue
        , Codes.n.value('codered[1]','varchar(max)') As CodeRed
        , Codes.n.value('codeyellow[1]','varchar(max)') As CodeYellow
from    #t t
        cross apply t.x.nodes('/code') as Codes(n);

Upvotes: 1

Related Questions