November 25, 2016 at 7:04 am
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>
November 25, 2016 at 7:09 am
Quick suggestion, skip the namespaces and use a wildcard *: prefix instead.
😎
November 25, 2016 at 7:16 am
Not sure I understood you - it will show xmlns='*' then, no?
November 25, 2016 at 7:48 am
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
November 25, 2016 at 7:52 am
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
November 25, 2016 at 7:53 am
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
November 25, 2016 at 7:59 am
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.
November 25, 2016 at 8:02 am
Can you post an example of the XML please?
😎
November 25, 2016 at 8:16 am
Eirikur Eiriksson (11/25/2016)
Can you post an example of the XML please?😎
Sure!
This is the valid example as of EHFstandards
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--
November 25, 2016 at 11:07 am
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
November 25, 2016 at 11:19 am
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