question

Mir123-7648 avatar image
0 Votes"
Mir123-7648 asked MelissaMa-msft commented

SQLServer WITH XMLNAMESPACES availability

Hi ,
in the documentation of WITH XMLNAMESPACES - https://docs.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

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Mir123-7648,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @Mir123-7648,

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:

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.