Import data from xml in MS SQL Server 2014

Question

Post reply

Import data from xml in MS SQL Server 2014

Hari-250426

Old Hand

Points: 378
More actions
December 6, 2016 at 3:38 am

#315581

Hi
The following is the code which does not give any results. Will highly appreciate if I get some help. Thanks in advance. //Hari
Expecting result as follows:
897;;825 1 3
897;;825 2 50
DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<?xml version="1.0" encoding="UTF-8"?>
<DespatchAdvice xmlns="urn:oasis:names:specification:ubl:schema:xsd:DespatchAdvice-2" xmlns:clm5639="urn:un:unece:uncefact:codelist:specification:5639:1988" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:ccts="urn:un:unece:uncefact:documentation:2" xmlns:cct="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:clmIANAMIMEMediaType="urn:un:unece:uncefact:codelist:specification:IANAMIMEMediaType:2003" xmlns:udt="urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:clm66411="urn:un:unece:uncefact:codelist:specification:66411:2001" xmlns:clm54217="urn:un:unece:uncefact:codelist:specification:54217:2001" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:qdt="urn:oasis:names:specification:ubl:schema:xsd:QualifiedDatatypes-2">
<cbc:UBLVersionID>2.1</cbc:UBLVersionID>
<cbc:ID>897;;825</cbc:ID>
<cbc:UUID>ceda8bec-5bc3-443f-85b3-1d22778ff601</cbc:UUID>
<cac:DespatchLine>
<cbc:ID>1</cbc:ID>
<cbc:DeliveredQuantity>3.000000</cbc:DeliveredQuantity>
<cac:OrderLineReference>
<cbc:SalesOrderLineID>10</cbc:SalesOrderLineID>
</cac:OrderLineReference>
</cac:DespatchLine>
<cac:DespatchLine>
<cbc:ID>2</cbc:ID>
<cbc:DeliveredQuantity>50.000000</cbc:DeliveredQuantity>
<cac:OrderLineReference>
<cbc:SalesOrderLineID>20</cbc:SalesOrderLineID>
</cac:OrderLineReference>
</cac:DespatchLine>
</DespatchAdvice>
'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc,
'<DespatchAdvice xmlns="urn:oasis:names:specification:ubl:schema:xsd:DespatchAdvice-2" xmlns:clm5639="urn:un:unece:uncefact:codelist:specification:5639:1988" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:ccts="urn:un:unece:uncefact:documentation:2" xmlns:cct="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:clmIANAMIMEMediaType="urn:un:unece:uncefact:codelist:specification:IANAMIMEMediaType:2003" xmlns:udt="urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:clm66411="urn:un:unece:uncefact:codelist:specification:66411:2001" xmlns:clm54217="urn:un:unece:uncefact:codelist:specification:54217:2001" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:qdt="urn:oasis:names:specification:ubl:schema:xsd:QualifiedDatatypes-2"/>'
SELECT *
INTO #TempEdge
FROM OPENXML (@idoc,'/DespatchAdvice/cac:DespatchLine',2)
WITH (picklist_packlist_no varchar(32) '../../cbc:ID',
linenumber int 'cbc:ID',
itempicked decimal(16,6) 'DeliveredQuantity',
status varchar(64),
finaldelivery varchar(1)
)
EXEC sp_xml_removedocument @idoc
select * from #TempEdge
drop table #TempEdge

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Quick suggestion, use XQuery, here is an example

😎

USE TEEST;

GO

SET NOCOUNT ON;

DECLARE @XML_DOC XML = '<?xml version="1.0" encoding="UTF-8"?>

<cbc:UBLVersionID>2.1</cbc:UBLVersionID>

<cbc:ID>897;;825</cbc:ID>

<cbc:UUID>ceda8bec-5bc3-443f-85b3-1d22778ff601</cbc:UUID>

<cac:DespatchLine>

<cbc:ID>1</cbc:ID>

<cbc:DeliveredQuantity>3.000000</cbc:DeliveredQuantity>

<cac:OrderLineReference>

<cbc:SalesOrderLineID>10</cbc:SalesOrderLineID>

</cac:OrderLineReference>

</cac:DespatchLine>

<cac:DespatchLine>

<cbc:ID>2</cbc:ID>

<cbc:DeliveredQuantity>50.000000</cbc:DeliveredQuantity>

<cac:OrderLineReference>

<cbc:SalesOrderLineID>20</cbc:SalesOrderLineID>

</cac:OrderLineReference>

</cac:DespatchLine>

</DespatchAdvice>

';

SELECT

XMLDOC.DATA.value('(*:ID/text())[1]','VARCHAR(50)') AS DespatchAdvice_ID

,DISPATCHLINE.DATA.value('(*:ID/text())[1]','INT') AS DespatchLine_ID

,DISPATCHLINE.DATA.value('(*:DeliveredQuantity/text())[1]','NUMERIC(18,6)') AS DeliveredQuantity

,DISPATCHLINE.DATA.value('(*:OrderLineReference/*:SalesOrderLineID/text())[1]','NUMERIC(18,6)') AS SalesOrderLineID

FROM @XML_DOC.nodes('*:DespatchAdvice') XMLDOC(DATA)

CROSS APPLY XMLDOC.DATA.nodes('*:DespatchLine') DISPATCHLINE(DATA);

Output

DespatchAdvice_ID DespatchLine_ID DeliveredQuantity SalesOrderLineID

------------------ --------------- ------------------ -----------------

897;;825 1 3.000000 10.000000

897;;825 2 50.000000 20.000000

Hari-250426 Old Hand Points: 378 More actions · Answer 2

Thanks so much.

Why doesn't Openxml work in this case?

//Hari

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 3

Hari-250426 (12/6/2016)
Thanks so much.
Why doesn't Openxml work in this case?
//Hari

Haven't had time to look at it and frankly, I wouldn't bother with sp_xml_preparedocument as the XQuery methor is much better.

😎

Hari-250426 Old Hand Points: 378 More actions · Answer 4

Your solution works pretty good. Simpler as well.

Just one question. Does this syntax work with older versions of SQL Server as all our clients won't be having SQL Server 2014?

Thanks once again.

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

Hari-250426 (12/8/2016)
Your solution works pretty good. Simpler as well.
Just one question. Does this syntax work with older versions of SQL Server as all our clients won't be having SQL Server 2014?
Thanks once again.

It will work on SQL Server 2005 and later

😎