Create where condition from XML value with multiple namespaces

Question

I'm looking to return value for my where condition from an XML. I'd like to return a value from messages table's Request column. Which is in XML data format. Unfortunately all I could achieve is retrieving nothing. Then I tried to put the value as a column but I always get nulls for the values

Here's an XML from Request column:

<InvoiceRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/InternalReuqests">
  <ActiveUserID xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</ActiveUserID>
  <LinqConfigId xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</LinqConfigId>
  <RequestHeaderInfo xmlns:d2p1="Fix.Services" xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">
    <d2p1:MapArchive i:nil="true" />
    <d2p1:HandledSuccessCategory>rscNone</d2p1:HandledSuccessCategory>
  </RequestHeaderInfo>
  <Username xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase" i:nil="true" />
  <SSID>S-1-6-25-123456789-123456789-123456789-12345</SSID>
  <miscdata xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    <d2p1:string>date:2020.02.26 08:27:00</d2p1:string>
    <d2p1:string>hours:0</d2p1:string>
    <d2p1:string>Ready:True</d2p1:string>
    <d2p1:string>disct:False</d2p1:string>
    <d2p1:string>extdisct:False</d2p1:string>
    <d2p1:string>Matmove:False</d2p1:string>
    <d2p1:string>Matlim:0</d2p1:string>
    <d2p1:string>Comments:</d2p1:string>
  </miscdata>
  <ffreeID>468545</ffreeID>
</InvoiceRequest>

here's my sql query:

   select id, Request.value('(/*:InvoiceRequest/*:ffreeID)[1]','varchar(max)')
     from messages

I thought I should get in the first column the id from the database, and next to it the value of the ffreeID, but the Request.value is always null.

Could anyone look into it what am I missing?

Thom A · Accepted Answer · 2020-03-03 18:33:17Z

1

You need for declare the default namespace, which for your xml is http://schemas.datacontract.org/2004/07/InternalReuqests:

--Sample XML
DECLARE @xml xml = '<InvoiceRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/InternalReuqests">
  <ActiveUserID xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</ActiveUserID>
  <LinqConfigId xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</LinqConfigId>
  <RequestHeaderInfo xmlns:d2p1="Fix.Services" xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">
    <d2p1:MapArchive i:nil="true" />
    <d2p1:HandledSuccessCategory>rscNone</d2p1:HandledSuccessCategory>
  </RequestHeaderInfo>
  <Username xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase" i:nil="true" />
  <SSID>S-1-6-25-123456789-123456789-123456789-12345</SSID>
  <miscdata xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    <d2p1:string>date:2020.02.26 08:27:00</d2p1:string>
    <d2p1:string>hours:0</d2p1:string>
    <d2p1:string>Ready:True</d2p1:string>
    <d2p1:string>disct:False</d2p1:string>
    <d2p1:string>extdisct:False</d2p1:string>
    <d2p1:string>Matmove:False</d2p1:string>
    <d2p1:string>Matlim:0</d2p1:string>
    <d2p1:string>Comments:</d2p1:string>
  </miscdata>
  <ffreeID>468545</ffreeID>
</InvoiceRequest>'; --Assumed this should be </InvoiceRequest>, not <InvoiceRequest>.

--Get value
WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/InternalReuqests')
SELECT X.Request.value('(/InvoiceRequest/ffreeID/text())[1]','int')
FROM (VALUES(@XML))X(Request);

answered Mar 3, 2020 at 18:33

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

5 Comments

user3420067 Over a year ago

Larnu, I'd like to ask for clarification: you put the xml into a variable. I have this xml data in the request column of every row in the message table. How can I declare the variable in that case?

user3420067 Over a year ago

I tried it out with:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/InternalReuqests') SELECT id,Request.value('(/InvoiceRequest/ffreeID/text())[1]','int') FROM messages

but couldn't make it work. still getting nulls as values

Thom A Over a year ago

"you put the xml into a variable" I needed a way to test the process, @user3420067 ; don't forget, I don't have access to your instance.

Thom A Over a year ago

"still getting nulls as values" This implies the XML you have and the XML you supplied are not the same. This does work against the example we have: DB<>Fiddle

user3420067 Over a year ago

Hi Larnu! Sorry for no replying based on Yitzhak interpretation it worked, thank you very much for your help!

Yitzhak Khabinsky · Accepted Answer · 2020-03-03 19:42:49Z

Here is another way by simulating a mock table. Everything else resembles @Larnu's solution. All credit goes to @Larnu.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Request XML);
INSERT INTO @tbl (Request)
VALUES
(N'<InvoiceRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
                xmlns="http://schemas.datacontract.org/2004/07/InternalReuqests">
    <ActiveUserID xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</ActiveUserID>
    <LinqConfigId xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</LinqConfigId>
    <RequestHeaderInfo xmlns:d2p1="Fix.Services"
                       xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">
        <d2p1:MapArchive i:nil="true"/>
        <d2p1:HandledSuccessCategory>rscNone</d2p1:HandledSuccessCategory>
    </RequestHeaderInfo>
    <Username xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase" i:nil="true"/>
    <SSID>S-1-6-25-123456789-123456789-123456789-12345</SSID>
    <miscdata xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
        <d2p1:string>date:2020.02.26 08:27:00</d2p1:string>
        <d2p1:string>hours:0</d2p1:string>
        <d2p1:string>Ready:True</d2p1:string>
        <d2p1:string>disct:False</d2p1:string>
        <d2p1:string>extdisct:False</d2p1:string>
        <d2p1:string>Matmove:False</d2p1:string>
        <d2p1:string>Matlim:0</d2p1:string>
        <d2p1:string>Comments:</d2p1:string>
    </miscdata>
    <ffreeID>468545</ffreeID>
</InvoiceRequest>');
-- DDL and sample data population, end

WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/InternalReuqests')
SELECT ID
    , c.value('(ffreeID/text())[1]','INT') AS ffreeID
FROM @tbl AS tbl
    CROSS APPLY tbl.Request.nodes('/InvoiceRequest') AS t(c);

Output

+----+---------+
| ID | ffreeID |
+----+---------+
|  1 |  468545 |
+----+---------+

Thank you very much for your help Yitzhak! It did work! Unfortunatelly I wasn't able to use Larnu's solution but this one worked find. I'll gave the answer to Larnu as you wrote, but your example was the one I could use. Take care!

Collectives™ on Stack Overflow

Create where condition from XML value with multiple namespaces

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related