How to prevent having XMLNS added to each and every record?

  • I need to generate and XML file, but the request is it has to be without having xmlns added to each and every line, as it increases the size of the xml.

    Is it possible to escape it, please? I think in Oracle it was dbms_xmldom.removeAttribute function. Anything similar in MSSQL (2014), please?

    First I run

    WITH XMLNAMESPACES (

    'urn:digi-inkoop:ubl:2.0:NL:1.6:UBL-NL-CommonBasicComponents-2' AS [nl-cbc],

    'urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2' AS udt,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,

    'http://www.w3.org/2001/XMLSchema-instance' AS xsi,

    'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS doc,

    DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'

    )

    Then - my SELECT statement and the result is below:

    --===== PART OF MY SELECT === ----

    SELECT @xmlstring = '<?xml version="1.0" encoding="UTF-8"?>' + (

    SELECT

    'Text2' AS 'cbc:Name',

    ....

    FOR XML PATH('cac:AdditionalItemProperty'), ELEMENTS XSINIL

    )

    SELECT @xmlstring

    --========== RESULT ============---

    <?xml version="1.0" encoding="UTF-8"?><cac:AdditionalItemProperty xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:doc="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:udt="urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2" xmlns:nl-cbc="urn:digi-inkoop:ubl:2.0:NL:1.6:UBL-NL-CommonBasicComponents-2"><cbc:Name>Text2</cbc:Name><cbc:Value>EK|J5221E|ABJ-DXB|21.12.2016|1605|0555</cbc:Value></cac:AdditionalItemProperty><cac:AdditionalItemProperty xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:doc="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:udt="urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2" xmlns:nl-cbc="urn:digi-inkoop:ubl:2.0:NL:1.6:UBL-NL-CommonBasicComponents-2"><cbc:Name>Text2</cbc:Name><cbc:Value>EK|J5221E|DXB-MNL|22.12.2016|1020|2230</cbc:Value></cac:AdditionalItemProperty>

  • Quick suggestion, skip the namespaces and use a wildcard *: prefix instead.

    😎

  • Not sure I understood you - it will show xmlns='*' then, no?

  • The xlmns is being included because you've specified the XSINIL directive. If you don't want it included, you'll have to get rid of the XSINIL directive.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (11/25/2016)


    The xlmns is being included because you've specified the XSINIL directive. If you don't want it included, you'll have to get rid of the XSINIL directive.

    Drew

    The result is the same after replacing XSINIL with TYPE

  • BOR15K (11/25/2016)


    Not sure I understood you - it will show xmlns='*' then, no?

    Not what I meant

    😎

    To clarify, skip this

    WITH XMLNAMESPACES (

    'urn:digi-inkoop:ubl:2.0:NL:1.6:UBL-NL-CommonBasicComponents-2' AS [nl-cbc],

    'urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2' AS udt,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,

    'http://www.w3.org/2001/XMLSchema-instance' AS xsi,

    'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS doc,

    DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'

    )

    and use this

    --===== PART OF MY SELECT === ----

    SELECT @xmlstring = '<?xml version="1.0" encoding="UTF-8"?>' + (

    SELECT

    'Text2' AS '*:Name',

    ....

    FOR XML PATH('*:AdditionalItemProperty'), ELEMENTS XSINIL

    )

    SELECT @xmlstring

  • Eirikur Eiriksson (11/25/2016)


    BOR15K (11/25/2016)


    Not sure I understood you - it will show xmlns='*' then, no?

    Not what I meant

    😎

    To clarify, skip this

    WITH XMLNAMESPACES (

    'urn:digi-inkoop:ubl:2.0:NL:1.6:UBL-NL-CommonBasicComponents-2' AS [nl-cbc],

    'urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2' AS udt,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,

    'http://www.w3.org/2001/XMLSchema-instance' AS xsi,

    'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS doc,

    DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'

    )

    and use this

    --===== PART OF MY SELECT === ----

    SELECT @xmlstring = '<?xml version="1.0" encoding="UTF-8"?>' + (

    SELECT

    'Text2' AS '*:Name',

    ....

    FOR XML PATH('*:AdditionalItemProperty'), ELEMENTS XSINIL

    )

    SELECT @xmlstring

    I would love to use it, but two issues I see:

    1) I am not allowed to change the names

    2) When I do use '*' I get XML name space prefix '*' declaration is missing for FOR XML row name error message

    Thank you for your help.

  • Can you post an example of the XML please?

    😎

  • Eirikur Eiriksson (11/25/2016)


    Can you post an example of the XML please?

    😎

    Sure!

    This is the valid example as of EHFstandards

    view-source:https://vefa.difi.no/ehf/guide/invoice-and-creditnote/2.0/en/files/example/BII05%20T10%20gyldig%20faktura.xml

    This is my test in SQL, where I provide for now flight details only:

    DECLARE @xmlstring VARCHAR(MAX)

    WITH XMLNAMESPACES (

    'urn:digi-inkoop:ubl:2.0:NL:1.6:UBL-NL-CommonBasicComponents-2' AS [nl-cbc],

    'urn:un:unece:uncefact:data:draft:UnqualifiedDataTypesSchemaModule:2' AS udt,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' AS ext,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' AS cbc,

    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' AS cac,

    'http://www.w3.org/2001/XMLSchema-instance' AS xsi,

    'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' AS doc,

    DEFAULT 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2'

    ), flight_deails AS (

    SELECT

    'Text2' AS cbc_Name,

    'EK|J5221E|DXB-MNL|22.12.2016|1020|2230' AS cbc_Value

    UNION ALL

    SELECT

    'Text2' AS cbc_Name,

    'EK|J5221E|ABJ-DXB|21.12.2016|1605|0555' AS cbc_Value

    )

    --SELECT @xmlstring = '<?xml version="1.0" encoding="UTF-8"?>' + (

    SELECT 'Vessel' AS 'cbc:Name',

    'TBC' AS 'cbc:Value'

    UNION ALL

    SELECT 'Department' AS 'cbc:Name',

    'TBC' AS 'cbc:Value'

    UNION ALL

    SELECT 'Employee' AS 'cbc:Name',

    'TBC' AS 'cbc:Value'

    UNION ALL

    SELECT 'Project' AS 'cbc:Name',

    'TBC' AS 'cbc:Value'

    UNION ALL

    SELECT 'Approver' AS 'cbc:Name',

    'TBC' AS 'cbc:Value'

    UNION ALL

    SELECT cbc_Name AS 'cbc_Name' ,

    cbc_Value AS 'cbc:Value'

    FROM flight_deails

    FOR XML PATH('cac:AdditionalItemProperty'), TYPE

    --)

    --SELECT @xmlstring

    ---- ELEMENTS XSINIL--

  • Have you tried adding a ROOT directive? That seems to remove most of the xmlns prefixes, but it may not give you the structure that you need.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (11/25/2016)


    Have you tried adding a ROOT directive? That seems to remove most of the xmlns prefixes, but it may not give you the structure that you need.

    Drew

    Thank you for the advise, but you're correct - it has changed the structure I am required to provide.

Viewing 11 posts - 1 through 10 (of 10 total)

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