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

Question

Post reply

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

BOR15K

SSCertifiable

Points: 5894
More actions
November 25, 2016 at 7:04 am

#332334

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>

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

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

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

😎

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

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

drew.allen SSC Guru Points: 76988 More actions · Answer 3

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

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

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

BOR15K SSCertifiable Points: 5894 More actions · Answer 6

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.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

Eirikur Eiriksson

SSC Guru

Points: 182912

November 25, 2016 at 8:02 am

#1914535

Can you post an example of the XML please?

😎

BOR15K SSCertifiable Points: 5894 More actions · Answer 8

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

drew.allen SSC Guru Points: 76988 More actions · Answer 9

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

BOR15K SSCertifiable Points: 5894 More actions · Answer 10

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.