Extracting Data from XML Column in SQL Database

Data and Databases
1

I’m working on a database that has an XML column and I’m having trouble getting the data out. The table is called ‘Purchase’ and the XML column is ‘XML_COL’. Here’s an example of the XML:

<ns1:Request xmlns:ns1="http://www.sample.com/hic/event/request"
xmlns:ns2="http://www.sample.com/hic/eventpayload/request">
  <ns1:createeventRequest>
    <ns1:eventPayLoad>
      <ns2:eventPayLoad>
        <Id>123456</Id>
      </ns2:eventPayLoad>
    </ns1:eventPayLoad>
  </ns1:createeventRequest>
</ns1:Request>

I tried executing the following SQL query:

SELECT * FROM purchase,
XMLTABLE ('$d/Request/createeventRequest/eventPayLoad/eventPayLoad' 
PASSING XML_COL AS "d" 
COLUMNS 
Id VARCHAR(20) PATH 'Id') AS a 
WHERE (a.Id LIKE '1234%');

But the query returns an empty result. I need to retrieve all data for a specific Id, and I’m not sure if I should include the namespaces in my query or if something else is off. Any help would be appreciated!

2

Your XML query is on the right track, but there are a few adjustments needed to make it work correctly. The main issue is that you’re not accounting for the namespaces in your XPath expression. Here’s a modified version that should work:

SELECT *
FROM purchase,
XMLTABLE(
    'declare namespace ns1=\"http://www.sample.com/hic/event/request\";
     declare namespace ns2=\"http://www.sample.com/hic/eventpayload/request\";
     /ns1:Request/ns1:createeventRequest/ns1:eventPayLoad/ns2:eventPayLoad'
    PASSING XML_COL
    COLUMNS 
    Id VARCHAR(20) PATH 'Id'
) AS a
WHERE a.Id LIKE '1234%';

This query declares the namespaces and uses them in the XPath. It should correctly extract the Id from your XML structure. Remember to adjust the WHERE clause as needed for your specific use case.

3

hey there dancingbutterfly! i had a similar issue b4. try using the WITH XMLNAMESPACES clause, it helped me out. something like:

WITH XMLNAMESPACES(
‘http://www.sample.com/hic/event/request’ AS ns1,
‘http://www.sample.com/hic/eventpayload/request’ AS ns2)
SELECT *
FROM purchase
CROSS APPLY XML_COL.nodes(‘/ns1:Request/ns1:createeventRequest/ns1:eventPayLoad/ns2:eventPayLoad’) AS T(c)
WHERE T.c.value(‘(Id)[1]’, ‘VARCHAR(20)’) LIKE ‘1234%’;

hope this helps!