December 6, 2016 at 3:38 am
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
December 6, 2016 at 4:22 am
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"?>
<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>
';
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
December 6, 2016 at 6:03 am
Thanks so much.
Why doesn't Openxml work in this case?
//Hari
December 8, 2016 at 3:47 am
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.
😎
December 8, 2016 at 10:18 am
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.
December 8, 2016 at 11:13 am
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
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply