September 17, 2018 at 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?
September 17, 2018 at 9:06 am
BOR15K - Monday, September 17, 2018 9:02 AMI 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?
😎
September 17, 2018 at 9:18 am
Eirikur Eiriksson - Monday, September 17, 2018 9:06 AMBOR15K - Monday, September 17, 2018 9:02 AMI 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;
September 17, 2018 at 9:52 am
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
September 17, 2018 at 9:56 am
drew.allen - Monday, September 17, 2018 9:52 AMYou'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?
September 17, 2018 at 10:08 am
If the code is dynamically built, yes,.
September 17, 2018 at 10:12 am
BOR15K - Monday, September 17, 2018 9:56 AMdrew.allen - Monday, September 17, 2018 9:52 AMYou'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