CODEWITHSUNDEEP
xmlsql-server-2008-r2xquery
Andy_Kears
Andy_Kears

Reputation: 43

SQL query xml attributes 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 (please note that I didn't design the database, so I've no real idea why this column isn't just in XML format, there are several other XML columns in the same database... go figure...) , and I want to query an attribute in the XML.

I've copied the XML below, what I would like to query is the p3:productId value. Currently I have this (from another question I asked on here):

WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2001/XMLSchema-instance'
                          ,'http://www.w3.org/2001/XMLSchema-instance' AS p3)
SELECT CAST(Data AS XML).value(N'(/manifest/p3:productList/product/productId)[1]',N'nvarchar(max)') 
FROM Content

But this just comes back with NULL for all rows.

Am sure it's something very similar but can't quite get my head around it, and don't have much experience querying XML (obviously!). 

<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: 2

Views: 412

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

The following code will extract each value of your xml (had to add closing tags). The .nodes() call would allow the same query, if there are many different <p3:product> elements in your <p3:productList>:

DECLARE @xml XML=
'<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" />
  </p3:productList>
</manifest>';

--Thq query

WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2001/XMLSchema-instance'
                          ,'http://www.w3.org/2001/XMLSchema-instance' AS p3)
SELECT @xml.value(N'(/manifest/@p3:manifestDateTime)[1]',N'datetime') AS manifestDateTime
      ,@xml.value(N'(/manifest/@p3:manifestVersion)[1]',N'nvarchar(max)') AS manifestVersion
      ,@xml.value(N'(/manifest/@p3:manifestId)[1]',N'nvarchar(max)') AS manifestId
      ,p.value(N'@p3:releaseDateTime',N'datetime') AS Product_releaseDateTime
      ,p.value(N'@p3:installSeqId',N'int') AS Product_installSeqId
      ,p.value(N'@p3:uninstallSeqId',N'int') AS Product_uninstallSeqId
      ,p.value(N'@p3:releaseNum',N'int') AS Product_releaseNum
      ,p.value(N'@p3:productType',N'nvarchar(max)') AS Product_productType
      ,p.value(N'@p3:productId',N'nvarchar(max)') AS Product_productId
      ,p.value(N'@p3:mfgCode',N'nvarchar(max)') AS Product_mfgCode
FROM @xml.nodes(N'/manifest/p3:productList/p3:product') AS A(p);

The result

+-------------------------+-----------------+------------+-------------------------+----------------------+------------------------+--------------------+---------------------+----------------------+-----------------+
| manifestDateTime        | manifestVersion | manifestId | Product_releaseDateTime | Product_installSeqId | Product_uninstallSeqId | Product_releaseNum | Product_productType | Product_productId    | Product_mfgCode |
+-------------------------+-----------------+------------+-------------------------+----------------------+------------------------+--------------------+---------------------+----------------------+-----------------+
| 2016-02-17 17:34:29.593 | 1.1             | Some_Data  | 2016-02-17 17:34:29.593 | 2                    | 2                      | 1                  | doc                 | WEDREZ020RRRP0GGG001 | GIRE            |
+-------------------------+-----------------+------------+-------------------------+----------------------+------------------------+--------------------+---------------------+----------------------+-----------------+

Upvotes: 2

Related Questions