CODEWITHSUNDEEP
sql-serverxmlsql-server-2008-r2xquery
Andy_Kears
Andy_Kears

Reputation: 43

SQL query xml data from varchar(max) column

I have a table called Content which has a varchar(max) column called data.

The data in this column is in XML format, and I want to query an attribute in the XML.

Below is the top couple of lines of the XML, it is the p3:manifestId I want to query so my output would basically be Some_Data (I've obviously desensitized this).

Is this possible?

<manifest xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xmlns:p3="http://www.w3.org/2001/XMLSchema-instance" 
      xmlns="http://www.w3.org/2001/XMLSchema-instance" 
      p3:manifestDateTime="2016-02-17T17:34:29.5925096Z" 
      p3:manifestVersion="1.1" p3:manifestId="Some_Data">
  <p3:productList>
    <p3:product p3:releaseDateTime="2016-02-17T17:34:29.5925096Z"         p3:installSeqId="2" p3:uninstallSeqId="2" p3:releaseNum="1" p3:productType="doc" p3:productId="WEDREZ020RRRP0GGG001" p3:mfgCode="GIRE">

Upvotes: 1

Views: 2181

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

The only thing which might be complicated here, are the namespaces. How is this XML generated? It is quite unusual to see the same namespace (http://www.w3.org/2001/XMLSchema-instance) as default and with two different aliases...

DECLARE @mockup TABLE(ID INT IDENTITY, YourXML VARCHAR(MAX));

INSERT INTO @mockup VALUES
('<manifest xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
          xmlns:p3="http://www.w3.org/2001/XMLSchema-instance" 
          xmlns="http://www.w3.org/2001/XMLSchema-instance" 
          p3:manifestDateTime="2016-02-17T17:34:29.5925096Z" 
          p3:manifestVersion="1.1" p3:manifestId="Some_Data">
   <!--Some more stuff-->
   </manifest>');

--This approach does not look at namespaces at all. I use the wildcard *:

SELECT CAST(YourXML AS XML).value(N'(/*:manifest/@*:manifestId)[1]',N'nvarchar(max)')
FROM @mockup;

--This approach declares all needed namespaces in advance (best approach in most cases):

WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2001/XMLSchema-instance'
                          ,'http://www.w3.org/2001/XMLSchema-instance' AS p3)
SELECT CAST(YourXML AS XML).value(N'(/manifest/@p3:manifestId)[1]',N'nvarchar(max)')
FROM @mockup;

--This approach is usefull, if you really do not need more than one value. I use one alias ns1 for all different occurances of this namespace:

SELECT CAST(YourXML AS XML).value(N'declare namespace ns1="http://www.w3.org/2001/XMLSchema-instance";
                                    (/ns1:manifest/@ns1:manifestId)[1]',N'nvarchar(max)')
FROM @mockup;

Upvotes: 1

Related Questions