CODEWITHSUNDEEP
sql-serverxmlnamespaces
Tom Baxter
Tom Baxter

Reputation: 2208

SQL Server - Query XML Column with Missing Namespace

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]

Upvotes: 1

Views: 3086

Answers (1)

JLRishe
JLRishe

Reputation: 101672

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

Upvotes: 3

Related Questions