How do I get a substring value from a Sql column of varbinary type?

Question

I am trying to get a substring, FIRST_NAME, attribute value from ADDRESS_XML column in a table using a simple select query. The column type is varbinary(1000).

GetXml is a custom method defined elsewhere which works fine.

My where clause is

dbo.GetXml(ADDRESS_XML).exist('xpathExpression') = 1

Following is one row of the ADDRESS_XML column:

<PostalAddress xmlns="http://tempuri.org/PostalAddress.xsd">
    <PostalAddress TITLE="Mr" FIRST_NAME="John" LAST_NAME="SMITH" 
                   ADDRESS="207a Stratford Road, Shirley" CITY="Solihull" 
                   COUNTY="West Midlands" POSTCODE="B90 3AH" 
                   COUNTRY="United Kingdom" CONTACT_INFO="" 
                   EMAIL_ADDRESS="" PHONENO="" />
    </PostalAddress>

What should XPath expression be ?

I am using SQL Server 2008 R2 Express The varbinary column is storing a string as hex, which is why I am using a proprietory GetXml() method on it.

***WHY*** are you storing perfectly valid XML in a `VARBINARY` column?!?!? Makes no sense at all..... if looks like XML, smells like XML, quacks like XML - it probably **IS** XML and should be treated as such and stored **as** `XML` ..... — marc_s, Sep 09 '14 at 13:02
I'm at work - I have no control over the bad practices of our codebase :) — , Sep 09 '14 at 13:15
Your questions is not clear. You talk about XML stored as varbinary but you have a function that returns XML so that part is already taken care of right? You want an XPath expression to get the value of `FIRST_NAME` and your example query uses `exist` in a where clause that is tests for existence of nodes instead of returning values. You can not return a value in `exist`. You could compare the value of `FIRST_NAME` against a constant or a variable but it does not look like that is what you are looking for. — Mikael Eriksson, Sep 09 '14 at 13:16
Ok, great, so how do I compare the value of FIRST_NAME against a constant as that is all I want to do. Should I use substring() ? — , Sep 09 '14 at 13:21

score 1 · Answer 1 · answered Sep 09 '14 at 13:30

1

You can use sql:variable() to use a variable in a predicate in the exist function.

declare @FirstName nvarchar(100) = 'John';

with xmlnamespaces(default 'http://tempuri.org/PostalAddress.xsd')
select *
from T
where dbo.GetXml(ADDRESS_XML).exist('/PostalAddress/PostalAddress[@FIRST_NAME = sql:variable("@FirstName")]') = 1;

Or against a constant value in the xPath

with xmlnamespaces(default 'http://tempuri.org/PostalAddress.xsd')
select *
from T
where dbo.GetXml(ADDRESS_XML).exist('/PostalAddress/PostalAddress[@FIRST_NAME = "John"]') = 1;

answered Sep 09 '14 at 13:30

Mikael Eriksson

136,425
22
210
281

Neither of the above suggestions returned any result set, but neither did I get any errors either! – Sep 10 '14 at 13:22
@dotNetBlackBelt Ok, really hard to tell what you did wrong there without some code that reproduces what you see. [Here is a SQL Fiddle](http://sqlfiddle.com/#!6/666e4/1) that proves it is indeed working as expected. You might just have missed some tiny detail like that XML is case sensitive. – Mikael Eriksson Sep 10 '14 at 17:19

score 0 · Answer 2 · answered Sep 09 '14 at 13:24

Since you asked, here is the XPath expression to get FIRST_NAME attribute :

/PostalAddress/PostalAddress/@FIRST_NAME

or if you meant to get <PostalAddress> having FIRST_NAME attribute value equals 1 :

/PostalAddress/PostalAddress[@FIRST_NAME='1']

You may need to register default namespace first (MSDN: Specifying Default Namespace). Though, I'm not sure how you intended to use the XPath exactly.

How do I get a substring value from a Sql column of varbinary type?

2 Answers2