Unable to import data from xml in MS SQL Server 2014

Question

Post reply

Unable to import data from xml in MS SQL Server 2014

Hari-250426

Old Hand

Points: 378
More actions
December 6, 2016 at 12:16 am

#315578

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>
<cbc:IssueDate>2016-12-05</cbc:IssueDate>
<cbc:IssueTime>08:01:03.4621258+01:00</cbc:IssueTime>
<cbc:LineCountNumeric>2</cbc:LineCountNumeric>
<cac:OrderReference>
<cbc:ID>27</cbc:ID>
<cbc:UUID>723690b3-ac0e-4a60-8145-f7a37e924131</cbc:UUID>
<cbc:IssueDate>2016-12-03</cbc:IssueDate>
<cbc:IssueTime>17:05:54.0000000+01:00</cbc:IssueTime>
</cac:OrderReference>
<cac:BuyerCustomerParty>
<cbc:SupplierAssignedAccountID>9100</cbc:SupplierAssignedAccountID>
<cac:Party>
<cac:PartyName>
<cbc:Name>Empire</cbc:Name>
</cac:PartyName>
</cac:Party>
</cac:BuyerCustomerParty>
<cac:SellerSupplierParty>
<cac:Party>
<cac:PartyName>
<cbc:Name>Electra Sweden AB</cbc:Name>
</cac:PartyName>
</cac:Party>
</cac:SellerSupplierParty>
<cac:OriginatorCustomerParty>
<cac:Party>
<cac:PartyName>
<cbc:Name>Blasta Jarnhandel AB</cbc:Name>
</cac:PartyName>
</cac:Party>
</cac:OriginatorCustomerParty>
<cac:Shipment>
<cac:Delivery>
<cac:DeliveryLocation>
<cac:Address>
<cbc:StreetName>Enkopingsvagen 6</cbc:StreetName>
<cbc:CityName>BALSTA</cbc:CityName>
<cbc:PostalZone>74652</cbc:PostalZone>
<cac:Country>
<cbc:IdentificationCode>SE</cbc:IdentificationCode>
</cac:Country>
</cac:Address>
</cac:DeliveryLocation>
</cac:Delivery>
</cac:Shipment>
<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'
)
EXEC sp_xml_removedocument @idoc
select * from #TempEdge
drop table #TempEdge

Viewing 3 posts - 1 through 2 (of 2 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

If you're using SSIS, why are you completing this task in SQL? Would it not be easier to use the XML Task?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

I will have a stored procedure that will read the xml file and populate data in a couple of tables for further processing. This sp will be called from the front end of my application by passing the xml content. The problem I am facing is that OPENXML is not populating the table.