Options to assign a dynamic content to XMLNAMESPACES area

  • 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?

  • 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

  • drew.allen - Thursday, October 11, 2018 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

    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