Dynamic "FOR XML PATH" in SQL 2014

  • I have seen several replies on the Net that it is not possible to have dynamic FOR XML PATH in SQL 2008, but I wonder if this is still the case in SQL 2014, please?

    I have a bit complex SELECT statement (just under 500 lines), which starts with WITH XMLNAMESPACES and ends with FOR XML PATH in question.
    Until recently I was sending FOR XML PATH('Invoice'),ELEMENTS XSINIL for both Invoices and Credit Notes (a customer had internal logic to distinguish between invoice and credit note).
    A new customer though requires FOR XML PATH to have 'Invoice' for invoices and 'CreditNote' for Credit Notes. I can obviously copy / paste the same code and only change the content of FOR XML PATH,
    but I wonder if there is better solution, please?

  • BOR15K - Monday, September 17, 2018 9:02 AM

    I have seen several replies on the Net that it is not possible to have dynamic FOR XML PATH in SQL 2008, but I wonder if this is still the case in SQL 2014, please?

    I have a bit complex SELECT statement (just under 500 lines), which starts with WITH XMLNAMESPACES and ends with FOR XML PATH in question.
    Until recently I was sending FOR XML PATH('Invoice'),ELEMENTS XSINIL for both Invoices and Credit Notes (a customer had internal logic to distinguish between invoice and credit note).
    A new customer though requires FOR XML PATH to have 'Invoice' for invoices and 'CreditNote' for Credit Notes. I can obviously copy / paste the same code and only change the content of FOR XML PATH,
    but I wonder if there is better solution, please?

    Can you elaborate further on this please? 
    😎

  • Eirikur Eiriksson - Monday, September 17, 2018 9:06 AM

    BOR15K - Monday, September 17, 2018 9:02 AM

    I have seen several replies on the Net that it is not possible to have dynamic FOR XML PATH in SQL 2008, but I wonder if this is still the case in SQL 2014, please?

    I have a bit complex SELECT statement (just under 500 lines), which starts with WITH XMLNAMESPACES and ends with FOR XML PATH in question.
    Until recently I was sending FOR XML PATH('Invoice'),ELEMENTS XSINIL for both Invoices and Credit Notes (a customer had internal logic to distinguish between invoice and credit note).
    A new customer though requires FOR XML PATH to have 'Invoice' for invoices and 'CreditNote' for Credit Notes. I can obviously copy / paste the same code and only change the content of FOR XML PATH,
    but I wonder if there is better solution, please?

    Can you elaborate further on this please? 
    😎

    Presently I have 

    SELECT *
    FROM myInvoicesTable i
    WHERE i.invoice_number = 1234
    FOR XML PATH('Invoice'),ELEMENTS XSINIL;

    Now I need to do something like

    DECLARE @v_is_it_credit_note BIT;
    SELECT @v_is_it_credit_note = i.is_credit_note
    FROM myInvoicesTable i
    WHERE i.invoice_number = 1234;

    IF @v_is_it_credit_note
    SELECT *
    FROM myInvoicesTable i
    WHERE i.invoice_number = 1234
    FOR XML PATH('CreditNote'),ELEMENTS XSINIL;
    ELSE 
    SELECT *
    FROM myInvoicesTable i
    WHERE i.invoice_number = 1234
    FOR XML PATH('Invoice'),ELEMENTS XSINIL;

    I wonder if there is easiest way, e.g. (and I know CASE doesn't work here, but only to clarify what I am after)

    SELECT *
    FROM myInvoicesTable i
    WHERE i.invoice_number = 1234
    FOR XML PATH(CASE i.is_credit_note WHEN 1 THEN 'CreditNote' ELSE  'Invoice' END),ELEMENTS XSINIL;

  • You've been around long enough to know that you should provide sample data and expected results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Monday, September 17, 2018 9:52 AM

    You've been around long enough to know that you should provide sample data and expected results.

    Drew

    I am not seeking for a specific solution, but a general question - is it possible to provide a dynamic value to FOR XML PATH in SQL 2014 onwards?

  • If the code is dynamically built, yes,.

  • BOR15K - Monday, September 17, 2018 9:56 AM

    drew.allen - Monday, September 17, 2018 9:52 AM

    You've been around long enough to know that you should provide sample data and expected results.

    Drew

    I am not seeking for a specific solution, but a general question - is it possible to provide a dynamic value to FOR XML PATH in SQL 2014 onwards?

    No, but I think it can be done without being dynamic.  I can't test it without sample data and expected results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply