none
Is it Possible to use XMLNAMESPACES and a CTE at the Same Time? RRS feed

  • Question

  • I wanted to use a CTE to prepare some non-XML columns by casting them to the XML data type, then in the main query, I wanted to use XMLNAMESPACES. I tried the following:

    use Logging
    
    ;WITH DATA AS (
    	SELECT
    		L.ID, 
    		CAST(T.ServiceRequest AS XML) AS Request,
    		CAST(T.ServiceResponse AS XML) AS Response
    	FROM ServiceLog L
    )
    WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS s)
    SELECT
    	L.StartDate, L.EndDate,
    	D.Request.value('
    		local-name((/s:Envelope/s:Body/*)[1])
    	', 'nvarchar(64)') AS 'RequestName', 
    	D.Response.value('
    		local-name((/s:Envelope/s:Body[not(s:Fault)]/*)[1])
    	', 'nvarchar(64)') AS 'ResponseName', 
    	D.Response.value('
    		local-name((/s:Envelope/s:Body/s:Fault/s:Detail/*)[1])
    	', 'nvarchar(64)') AS 'FaultName',
    	D.Request,
    	D.Response
    FROM DATA D
    INNER JOIN LogData L ON D.ID = L.ID
    AND L.StartDate > DATEADD(MINUTE, -15, GETDATE())
    ORDER BY L.StartDate
    
    

    Unfortunately, I get the errors:

    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'WITH'.
    Msg 319, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    Is there some trick of syntax I'm missing?


    John Saunders
    WCF is Web Services. They are not two separate things.
    Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
    Use File->New Project to create Web Service Projects
    Saturday, July 31, 2010 5:30 AM

Answers

  • John,

    The WITH XMLNAMESPACES clause must precede the common table expression.

    ;WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS s)
    , DATA AS (
    ...
    )
    SELECT ...

    Example:

    USE Northwind;
    GO
    ;WITH XMLNAMESPACES ('uri' AS ns1)
    ,RS AS (
    SELECT
      OrderID,
      CustomerID,
      OrderDate,
      ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate, OrderID) AS CustomerOrderNumber
    FROM
      dbo.Orders
    )
    SELECT
      CustomerID, OrderID, OrderDate, CustomerOrderNumber
    FROM
      RS
    ORDER BY
      customerID, CustomerOrderNumber
    FOR XML RAW('ns1:Orders'), ELEMENTS;
    GO
    

    AMB

    • Marked as answer by John Saunders Saturday, July 31, 2010 5:24 PM
    Saturday, July 31, 2010 2:50 PM
    Moderator

All replies

  • John,

    The following passes syntax check. You only have to use "WITH" for the first CTE. Let us now if works.

    ;WITH DATA AS (
    	SELECT
    		L.ID, 
    		CAST(T.ServiceRequest AS XML) AS Request,
    		CAST(T.ServiceResponse AS XML) AS Response
    	FROM ServiceLog L
    ),
    XMLNAMESPACES AS (SELECT 'http://www.w3.org/2003/05/soap-envelope' AS s)
    SELECT
    	L.StartDate, L.EndDate,
    	D.Request.value('
    		local-name((/s:Envelope/s:Body/*)[1])
    	', 'nvarchar(64)') AS 'RequestName', 
    	D.Response.value('
    		local-name((/s:Envelope/s:Body[not(s:Fault)]/*)[1])
    	', 'nvarchar(64)') AS 'ResponseName', 
    	D.Response.value('
    		local-name((/s:Envelope/s:Body/s:Fault/s:Detail/*)[1])
    	', 'nvarchar(64)') AS 'FaultName',
    	D.Request,
    	D.Response
    FROM DATA D
    INNER JOIN LogData L ON D.ID = L.ID
    AND L.StartDate > DATEADD(MINUTE, -15, GETDATE())
    ORDER BY L.StartDate
    

    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Saturday, July 31, 2010 6:30 AM
    Moderator
  • John,

    The WITH XMLNAMESPACES clause must precede the common table expression.

    ;WITH XMLNAMESPACES ('http://www.w3.org/2003/05/soap-envelope' AS s)
    , DATA AS (
    ...
    )
    SELECT ...

    Example:

    USE Northwind;
    GO
    ;WITH XMLNAMESPACES ('uri' AS ns1)
    ,RS AS (
    SELECT
      OrderID,
      CustomerID,
      OrderDate,
      ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate, OrderID) AS CustomerOrderNumber
    FROM
      dbo.Orders
    )
    SELECT
      CustomerID, OrderID, OrderDate, CustomerOrderNumber
    FROM
      RS
    ORDER BY
      customerID, CustomerOrderNumber
    FOR XML RAW('ns1:Orders'), ELEMENTS;
    GO
    

    AMB

    • Marked as answer by John Saunders Saturday, July 31, 2010 5:24 PM
    Saturday, July 31, 2010 2:50 PM
    Moderator
  • That worked perfectly, thanks!

    Was that documented somewhere in BOL? I couldn't find anything either there, or online.

     


    John Saunders
    WCF is Web Services. They are not two separate things.
    Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
    Use File->New Project to create Web Service Projects
    Saturday, July 31, 2010 5:24 PM
  • What happens when you run the script I posted? Thanks.
    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    Saturday, July 31, 2010 5:42 PM
    Moderator
  • Msg 2229, Level 16, State 1, Line 16

    XQuery [DATA.Request.value()]: The name "s" does not denote a namespace


    John Saunders
    WCF is Web Services. They are not two separate things.
    Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
    Use File->New Project to create Web Service Projects
    Saturday, July 31, 2010 6:22 PM
  • > Was that documented somewhere in BOL? I couldn't find anything either there, or online.

    In Books Online 2008 I found this sentence in the topic for WITH XMLNAMESPACES:

    When you use the WITH XMLNAMESPACES clause in a statement that also includes a common table expression, the WITH XMLNAMESPACES clause must precede the common table expression in the statement.

    I have not check Books Online for SQL 2005.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Saturday, July 31, 2010 8:46 PM
  • Thanks, Erland, I guess I looked through it.

    John Saunders
    WCF is Web Services. They are not two separate things.
    Use WCF for All New Web Service Development, instead of legacy ASMX or obsolete WSE
    Use File->New Project to create Web Service Projects
    Monday, August 2, 2010 6:57 AM
  • wow, thanks for the tip

    I needed to do a query like this too.

    ;with xmlnamespaces(default 'http://www.company.com/messages...'),

      cte as (select C.value('MessageID[1]', 'varchar(50)') as MessageID from XmlTable

                 cross apply xmlpayload.nodes('/Message/Header') as T2(C) )

      select * from cte

      where MessageID = '....';

    Friday, April 19, 2013 12:27 AM