SQL Server - Query XML Column with Missing Namespace

Question

This problem is really nasty it seems. I have the following small XML in a column named, "Message", and I'd like to query against it. The problem I'm having is with the "ClaimData" element. As you can see, it sets its namespace to an empty string.

<DataExchange xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns="http://schemas.abc.com/library/DataExchange">
  <Data>
      <ClaimData xmlns="">
          <CurrentClaimNumber>TEST0000319001</CurrentClaimNumber>
      </ClaimData>
  </Data>
</DataExchange>

The query below is returning NULL values for the XML column because the ClaimData element uses an empty namespace, and I do not know how to indicate this in the query of the XML column.

Can someone give a working (and hopefully, tested) example of how to query the "CurrentClaimNumber" element (which is a child of "ClaimData") in the XML Message column?

Thanks very, very much.

WITH XMLNAMESPACES (
     'http://www.w3.org/2001/XMLSchema' AS "xsd",
    'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
     DEFAULT 'http://schemas.rising.com/library/DataExchange'
) SELECT [StoredMessageID],
    Message.value('(/DataExchange/Data/ClaimData/CurrentClaimNumber)[1]', 'CHAR(750)')
  FROM [DataExchange].[dbo].[MessageStorage]

JLRishe · Accepted Answer · 2013-03-14 13:38:27Z

3

How about just assigning the default namespace to a prefix so that you can use the null namespace without one? That might be the only option:

WITH XMLNAMESPACES (
     'http://www.w3.org/2001/XMLSchema' AS "xsd",
    'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
     'http://schemas.abc.com/library/DataExchange' AS "de"
) SELECT [StoredMessageID],
    Message.value('(/de:DataExchange/de:Data/ClaimData/CurrentClaimNumber)[1]', 
                                                                         'CHAR(750)')
  FROM [DataExchange].[dbo].[MessageStorage]

Verified as follows:

insert into MessageStorage values('
<DataExchange
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns="http://schemas.abc.com/library/DataExchange">
  <Data>
      <ClaimData xmlns="">
          <CurrentClaimNumber>TEST0000319001</CurrentClaimNumber>
      </ClaimData>
  </Data>
</DataExchange>');


WITH XMLNAMESPACES (
     'http://www.w3.org/2001/XMLSchema' AS "xsd",
    'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
     'http://schemas.abc.com/library/DataExchange' AS "de"
) SELECT [StoredMessageID],
    Message.value('(/de:DataExchange/de:Data/ClaimData/CurrentClaimNumber)[1]', 
                                                                         'CHAR(750)')
  FROM [dbo].[MessageStorage]

Result:

 1     TEST0000319001

edited Mar 14, 2013 at 13:38

answered Mar 14, 2013 at 3:56

JLRishe

102k19 gold badges138 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tom Baxter Over a year ago

My original XML was slightly wrong. The namespaces should appear within the openeing DataExchange element. I don't think you noticed that though in your response. Your solution does not work because the ClaimData element is NOT using the default namespace. It's using an empty (i.e., missing) namespace.

JLRishe Over a year ago

It's using a null namespace. I'm aware of that. If you specify a default namespace, you won't be able to access the null namespace. Did you try my solution, or just assume it doesn't work?

Tom Baxter Over a year ago

I did try but it seems I made a mistake. Indeed it does work. Thank you very much. This has saved me a lot of time.

Collectives™ on Stack Overflow

SQL Server - Query XML Column with Missing Namespace

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related