Unable to import data from xml to MS SQL Server 2014

Transact-SQL
1

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

cbc:UBLVersionID2.1</cbc:UBLVersionID>
cbc:ID897;;825</cbc:ID>
cbc:UUIDceda8bec-5bc3-443f-85b3-1d22778ff601</cbc:UUID>
cbc:IssueDate2016-12-05</cbc:IssueDate>
cbc:IssueTime08:01:03.4621258+01:00</cbc:IssueTime>
cbc:LineCountNumeric2</cbc:LineCountNumeric>
cac:OrderReference
cbc:ID27</cbc:ID>
cbc:UUID723690b3-ac0e-4a60-8145-f7a37e924131</cbc:UUID>
cbc:IssueDate2016-12-03</cbc:IssueDate>
cbc:IssueTime17:05:54.0000000+01:00</cbc:IssueTime>
</cac:OrderReference>
cac:BuyerCustomerParty
cbc:SupplierAssignedAccountID9100</cbc:SupplierAssignedAccountID>
cac:Party
cac:PartyName
cbc:NameEmpire</cbc:Name>
</cac:PartyName>
</cac:Party>
</cac:BuyerCustomerParty>
cac:SellerSupplierParty
cac:Party
cac:PartyName
cbc:NameElectra Sweden AB</cbc:Name>
</cac:PartyName>
</cac:Party>
</cac:SellerSupplierParty>
cac:OriginatorCustomerParty
cac:Party
cac:PartyName
cbc:NameBlasta Jarnhandel AB</cbc:Name>
</cac:PartyName>
</cac:Party>
</cac:OriginatorCustomerParty>
cac:Shipment
cac:Delivery
cac:DeliveryLocation
cac:Address
cbc:StreetNameEnkopingsvagen 6</cbc:StreetName>
cbc:CityNameBALSTA</cbc:CityName>
cbc:PostalZone74652</cbc:PostalZone>
cac:Country
cbc:IdentificationCodeSE</cbc:IdentificationCode>
</cac:Country>
</cac:Address>
</cac:DeliveryLocation>
</cac:Delivery>
</cac:Shipment>
cac:DespatchLine
cbc:ID1</cbc:ID>
cbc:DeliveredQuantity3.000000</cbc:DeliveredQuantity>
cac:OrderLineReference
cbc:SalesOrderLineID10</cbc:SalesOrderLineID>
</cac:OrderLineReference>
</cac:DespatchLine>
cac:DespatchLine
cbc:ID2</cbc:ID>
cbc:DeliveredQuantity50.000000</cbc:DeliveredQuantity>
cac:OrderLineReference
cbc:SalesOrderLineID20</cbc:SalesOrderLineID>
</cac:OrderLineReference>
</cac:DespatchLine>

'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc,
''

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

2

Presume the MarkDown on this site has "eaten" all the XML tags in your sample.

You could try editing (or replying if EDIT is no longer available to you) surrounding your code with

    ```sql
    you code here
    ```
3

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
4

Removing that from @doc seems to cheer it up. Some other changes:

FROM OPENXML (@idoc,'/DespatchAdvice/cac:DespatchLine',2)
WITH (
picklist_packlist_no varchar(32) '../cbc:ID',
linenumber varchar(32) 'cbc:ID',
itempicked decimal(16,6) 'cbc:DeliveredQuantity',
status varchar(64),
finaldelivery varchar(1)
)

but I know next to squat about XML, so these suggestions may not be much to go on