SQL Server: How do I check if an element attribute of an xml node contains a specific string?

Question

I've a xml document like this.

<Students>
  <Student>
    <Node Type="http://www.something.com/xyz/10/abc/RollNumber" 
          Value="Original=10000;Modified=100003445" />
    <Node Type="http://www.something.com/xyz/10/abc/SerialNumber" 
          Value="Original=10000;Modified=172777" />
    <Node Type="http://www.something.com/xyz/10/abc/Stream" 
          Value="Original=CS;Modified=EC" />
  </Student>
</Students>

To get the value of the stream, I can write:

Select @MyXML.value('(/Students/Student/Node[@Type="http://www.something.com/xyz/10/abc/Stream"]/@Value)[1]', 'varchar(100)') AS ChangedValue

My problem is that the url part may change, hence I want to match it based on some criteria like

@Type = "%Stream"

or

@Type.Contains("Stream")

Is it possible?

score 4 · Accepted Answer · edited May 23 '17 at 11:51

The xpath engine in SQL supports the contains function, so you can say:

SELECT
    @MyXML.value('(/Students/Student/Node[contains(@Type,"Stream")]/@Value)[1]',
                 'varchar(100)') 
    AS ChangedValue

Unfortunately however, the obvious ends-with() is an xpath 2.0 function, and the SQL engine doesn't offer that, so if you wanted to test for the string ending with Stream you'd have to say (as per this post):

SELECT
    @MyXML.value
   ('(/Students/Student/Node["Stream"
                = substring(@Type, string-length(@Type)-5)]/@Value)[1]',
     'varchar(100)') 
    AS ChangedValue

SQL Server: How do I check if an element attribute of an xml node contains a specific string?

1 Answers1