CODEWITHSUNDEEP
sqlxquery
Tys
Tys

Reputation: 3610

SQL Server XQuery, how do i select a value

I have XML type column in my SQL Server table, and the XML document that is in that table looks like: 

<?xml version="1.0" encoding="utf-16"?>  
<Product xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    
    <Key xmlns="urn:microsoft-dynamics-schemas/page/product">48;nyI9AAJ7/1AAVQBCADYAAAACe/8xAC4AMAAwAAAAAIfei8sC11;152438283530;</Key>    
    <Code xmlns="urn:microsoft-dynamics-schemas/page/product">PR238</Code>    
    <Version xmlns="urn:microsoft-dynamics-schemas/page/product">1.00</Version>    
    <ExternalRefNr xmlns="urn:microsoft-dynamics-schemas/page/product">46894046</ExternalRefNr>    
    <Name xmlns="urn:microsoft-dynamics-schemas/page/product">Nailgun XJ-3</Name>  
</Product>

Now, in a SQL Server (X) Query I want to select the name of this product (Nailgun XJ-3), but whatever I try, I always get an empty result.

I guess it should be something simple, like the line below, but I'm missing something. Perhaps it's the handling of the namespace, but I couldn't find a proper way to do that. 

SELECT ProductDetailSourceXML.query('//Name') FROM Product

Upvotes: 0

Views: 615

Answers (1)

marc_s
marc_s

Reputation: 754388

Yes it's the XML namespace - since it's there, you must respect it and deal with it properly!

Try this:

DECLARE @tbl TABLE (ID INT NOT NULL, ProductsDetailXML XML)

INSERT INTO @tbl VALUES(1, N'<?xml version="1.0" encoding="utf-16"?>  
<Product xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    
    <Key xmlns="urn:microsoft-dynamics-schemas/page/product">48;nyI9AAJ7/1AAVQBCADYAAAACe/8xAC4AMAAwAAAAAIfei8sC11;152438283530;</Key>    
    <Code xmlns="urn:microsoft-dynamics-schemas/page/product">PR238</Code>    
    <Version xmlns="urn:microsoft-dynamics-schemas/page/product">1.00</Version>    
    <ExternalRefNr xmlns="urn:microsoft-dynamics-schemas/page/product">46894046</ExternalRefNr>    
    <Name xmlns="urn:microsoft-dynamics-schemas/page/product">Nailgun XJ-3</Name>  
</Product>')

-- define the XML namespace and a prefix to use in your XQuery    
;WITH XMLNAMESPACES('urn:microsoft-dynamics-schemas/page/product' AS ns)
SELECT
    ID,
    -- use the XML namespace prefix where needed!
    ProductName = XC.value('(ns:Name)[1]', 'varchar(50)')
FROM 
    @tbl
CROSS APPLY 
    ProductsDetailXML.nodes('/Product') AS XT(XC)
WHERE
    ID = 1

Upvotes: 2

Related Questions