SQLServer WITH XMLNAMESPACES availability

Question

Hi ,
in the documentation of WITH XMLNAMESPACES - https://learn.microsoft.com/en-us/sql/t-sql/xml/with-xmlnamespaces?view=sql-server-ver15

I can't find a definition of where I can use it, and it is missing from a documentation of ccommands,
like -
In SELECT we have it in the documentation , and in UPDATE/INSERT/DELETE no , while it looks like working in those commands.

I want to know what all of the places I can use it , or the answer is everywhere.

Thanks

Answer 1

You use it together with XQuery, see XQuery Language Reference (SQL Server)

Example to query on ReportServer database to get data sets:

-- List datasets with command text for all SSRS reports   
;WITH    
 XMLNAMESPACES   
     (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'   
             ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'   
      AS rd)   
,DEF AS   
    (SELECT RPT.ReportPath   
           ,R.RptNode.value('(./Query/DataSourceName)[1]', 'nvarchar(425)') AS DataSourceName   
           ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName   
           ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)')))    
                    ,'>', '>')   
                    ,'&lt;', '<')   
            AS CommandText   
     FROM (SELECT RPT.Path AS ReportPath   
                 ,RPT.name AS ReportName   
                 ,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML   
           FROM ReportServer.dbo.[Catalog] AS RPT   
           WHERE RPT.Type = 2  -- 2 = Reports   
         ) AS RPT   
     CROSS APPLY RPT.contentXML.nodes('/Report/DataSets/DataSet') AS R(RptNode)   
    )   
SELECT DEF.ReportPath   
      ,DEF.DataSourceName   
      ,DEF.DataSetName   
      ,DEF.CommandText   
FROM DEF   
-- Optional filter:   
-- WHERE DEF.CommandText LIKE '%/[Team System/]%' -- MDX query against TFS cube   
ORDER BY DEF.ReportPath   
        ,DEF.DataSourceName   
        ,DEF.DataSetName  

Answer 2

Hi @Mir123 ,

Welcome to Microsoft Q&A!

WITH XMLNAMESPACES provides namespace URI support in the following way:

• It makes the namespace prefix to URI mapping available when Constructing XML Using FOR XML queries.
• It makes the namespace to URI mapping available to the static namespace context of the xml Data Type Methods.

Then you could use it in it in XML Queries as below:

• Add Namespaces to Queries with WITH XMLNAMESPACES.
• Specifying default namespaces.

You could also refer XML data type methods without and with using WITH XMLNAMESPACES as below:

SELECT ProductModelID, CatalogDescription.query('  
declare namespace pd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
    <Product  
        ProductModelID= "{ sql:column("ProductModelID") }"  
        />  
') AS Result  
FROM Production.ProductModel  
WHERE CatalogDescription.exist('  
    declare namespace  pd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";  
     /pd:ProductDescription[(pd:Specifications)]'  
    ) = 1  

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' as pd)  
SELECT ProductModelID, CatalogDescription.query('  
    <Product  
        ProductModelID= "{ sql:column("ProductModelID") }"  
        />  
') AS Result  
FROM Production.ProductModel  
WHERE CatalogDescription.exist('  
     /pd:ProductDescription[(pd:Specifications)]'  
    ) = 1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.