October 11, 2018 at 6:43 am
I have a long running process, generating XML files with XML Namespaces, which are defined using WITH XMLNAMESPACES as below:
WITH XMLNAMESPACES (
'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS [nl-cbc],
'urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2' AS udt,
'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,
'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext,
'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS doc,
'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'
)
I have now a new request to add a leading Invoice or CreditNote respectively, so it will start like 'Invoice urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2
or 'CreditNote urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2
I can think of the following three options, but I wonder if there is something simpler, please.
Option 1
Have an IF statement and repeat the whole select statement of over 200 rows:
IF @is_it_credit_note = 'N'
WITH XMLNAMESPACES (
'Invoice urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS [nl-cbc],
'urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2' AS udt,
'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,
'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext,
'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS doc,
'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'
)
SET @v_xml = (SELECT....)
IF @is_it_credit_note = 'Y'
WITH XMLNAMESPACES (
'CreditNote urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS [nl-cbc],
'urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2' AS udt,
'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,
'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext,
'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS doc,
'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'
)
SET @v_xml = (SELECT....)
Option 2
To make the whole code dynamic and to control xmlnamespaces area via a variable
Option 3
to execute the whole statement and then to only replace the xmlnamespaces
DECLARE @v_xmlnamespace VARCHAR(100) = 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',
@v_invoice_xmlnamespace VARCHAR(100) = 'Invoice urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',
@v_credit_xmlnamespace VARCHAR(100) = 'CreditNote urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',
WITH XMLNAMESPACES (
'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS [nl-cbc],
'urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2' AS udt,
'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,
'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,
'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext,
'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS doc,
'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'
)
SET @v_xml = (SELECT....)
SELECT CAST(REPLACE(@v_xml, @v_xmlnamespace,CASE @is_this_invoice WHEN 'Y' THEN @v_invoice_xmlnamespace ELSE @v_credit_xmlnamespace END) AS XML)
Currently I am going with the last option, but wonder if there is anything simpler, please?
October 11, 2018 at 8:12 am
Why don't you just have two separate namespaces? The whole reason for introducing namespaces in the first place was so that you could distinguish between similar elements from two different sources. Trying to cram two different sources into the same namespace defeats that purpose.
WITH XMLNAMESPACES (
'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS [nl-credit],
'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS [nl-invoice],
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2018 at 8:19 am
drew.allen - Thursday, October 11, 2018 8:12 AMWhy don't you just have two separate namespaces? The whole reason for introducing namespaces in the first place was so that you could distinguish between similar elements from two different sources. Trying to cram two different sources into the same namespace defeats that purpose.
WITH XMLNAMESPACES (
'urn:oasis:names:specification:ubl:schema:xsd:CreditNote-2' AS [nl-credit],
'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS [nl-invoice],Drew
Do you mean I can control [nl-credit] / [nl-invoice] logic?
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply