I am having trouble with importing an XML file generated by our factory machine into a SQL table. If I edit the file to remove the additional Namespaces I get the right results but as we want to automate this process that isn't feasible to do.
The start of the XML file looks like this;
<?xml version="1.0" encoding="UTF-8"?>
<LaneHistory xmlns="omlNamespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="omlNamespace C:\Program%20Files\Moba\Omnia%20MMI\MMI\bin\..\Application%20Data\XML\Schema\OML-LaneHistory.xsd">
<FileProperties>
<MachineNumber>33309</MachineNumber>
<CreationDateTime>2012-06-29T08:12:41</CreationDateTime>
<ContentDescription>LaneHistory</ContentDescription>
<Version>001</Version>
</FileProperties>
<Lane>
<General>
<StartDateTime>2012-06-29T05:50:02</StartDateTime>
<StopDateTime>2012-06-29T06:20:07</StopDateTime>
<LaneNumber>25</LaneNumber>
</General>
<Supply>
<Property>
<Name>Jones Heulyn Org Hse 3</Name>
<AdditionalCode>UK20296=</AdditionalCode>
</Property>
<Identification>
<TraceCode1>UK20296=</TraceCode1>
<TraceCode2>633264</TraceCode2>
I have tried the following;
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX), @rootxmlns varchar(MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
set @rootxmlns = '<LaneHistory xmlns="omlNamespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="omlNamespace C:\Program%20Files\Moba\Omnia%20MMI\MMI\bin\..\Application%20Data\XML\Schema\OML-LaneHistory.xsd">/'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, @rootxmlns
but get the error ;
The XML parse error 0xc00ce553 occurred on line number 0, near the XML text "". Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1 [Batch Start Line 33] The error description is 'The following tags were not closed: LaneHistory.'.
Can anyone point me in the right direction.
Cheers jason
*** ADDITIONAL
The rest of my statement is as follows;
SELECT *
FROM OPENXML(@hDoc,'LaneHistory/Lane/Results/ResultPerGrade/WeightLimit')
WITH
(
MachineNumber [varchar](7) '//FileProperties/MachineNumber',
StartDateTime [varchar](25) '../../../General/StartDateTime',
StopDateTime [varchar](125) '../../../General/StopDateTime',
LaneNumber [varchar](6) '../../../General/LaneNumber',
ExternalIdentification [varchar](30) '../../../Supply/ExternalIdentification/ID1',
FlkName [varchar](30) '../../../Supply/Property/Name',
FlkNumber [varchar](10) '../../../Supply/Identification/TraceCode2',
UKCode [varchar](10) '../../../Supply/Identification/TraceCode1',
ProductName [VarChar](30) '../../../Product/Property/Name',
PRoductType [VarChar](30) '../../../Product/Property/Type',
Package [VarChar](30) '../../../Product/Property/PackageName',
EggsPerLane [varchar](6) '../Count',
EggsGraded [FLOAT] '../../../Supply/Operation/AmountOfEggs',
EDescription [VarChar](30) '../Description',
EWeight [VarChar](30) '../Weight',
MinWeight [VarChar](30) '../WeightLimit/MinWeight',
MaxWeight [VarChar](30) '../WeightLimit/MaxWeight',
ExtraInfo1 [VarChar](20) '../../../Supply/Extra/Info1',
ExtraInfo2 [VarChar](20) '../../../Supply/Extra/Info2',
ExtraInfo3 [VarChar](20) '../../../Supply/Extra/Info3',
ExtraInfo4 [VarChar](20) '../../../Supply/Extra/Info4',
ExtraInfo5 [VarChar](20) '../../../Supply/Extra/Info5',
ShedNo [VarChar](6) '../../../Supply/Egg/ShedNumber',
ShedType [VarChar](30) '../../../Supply/Egg/Type',
LayDate [VarChar](12) '../../../Supply/Dates/LayDate',
SupplyDate [VarChar](12) '../../../Supply/Dates/SupplyDate',
BestBefore [VarChar](12) '../../../Product/Dates/BestBeforeDate'
)
EXEC sp_xml_removedocument @hDoc
It looks to me like your namespace is badly formed:
set @rootxmlns = '<LaneHistory xmlns="omlNamespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="omlNamespace C:\Program%20Files\Moba\Omnia%20MMI\MMI\bin\..\Application%20Data\XML\Schema\OML-LaneHistory.xsd">/'
You have the "/" and ">" at the end in the wrong order. I think you just need this instead:
set @rootxmlns = '<LaneHistory xmlns="omlNamespace" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="omlNamespace C:\Program%20Files\Moba\Omnia%20MMI\MMI\bin\..\Application%20Data\XML\Schema\OML-LaneHistory.xsd"/>'
MSDN has an example XML namespace of:
'<ROOT xmlns:xyz="urn:MyNamespace"/>'
...for reference.
Part 2 - so your query returns nothing, I think this is because you have the attribute-centric option (1) set when you use OPENXML to query your XML. If you change this to element-centric option (2) then it should work? I did a little test with your partial XML and could pop out the Machine Number by changing the option from 1 to 2.
In very basic terms, this is attribute-centric XML:
<LaneHistory><FileProperties MachineNumber="33309"/></LaneHistory>
...and this is element-centric XML;
<LaneHistory><FileProperties><MachineNumber>33309</MachineNumber></FileProperties></LaneHistory>
So, to be clear, your OPENXML statement needs a third parameter that should be a 2.
I finally fixed the issue I had to change the code to the following;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML,N'<root xmlns:n="omlNamespace" />'
insert into lanehistory
SELECT * FROM OPENXML (@hDoc,'/n:LaneHistory/n:Lane/n:Results/n:ResultPerGrade/n:WeightLimit', 2)
WITH
(
MachineNumber [varchar](7) '../../../../n:FileProperties/n:MachineNumber',
StartDateTime [varchar](25) '../../../n:General/n:StartDateTime',
StopDateTime [varchar](125) '../../../n:General/n:StopDateTime',
LaneNumber [varchar](6) '../../../n:General/n:LaneNumber',
ExternalIdentification [varchar](30) '../../../n:Supply/n:ExternalIdentification/n:ID1',
FlkName [varchar](30) '../../../n:Supply/n:Property/n:Name',
FlkNumber [varchar](10) '../../../n:Supply/n:Identification/n:TraceCode2',
UKCode [varchar](10) '../../../n:Supply/n:Identification/n:TraceCode1',
ProductName [VarChar](30) '../../../n:Product/n:Property/n:Name',
PRoductType [VarChar](30) '../../../n:Product/n:Property/n:Type',
Package [VarChar](30) '../../../n:Product/n:Property/n:PackageName',
EggsPerLane [varchar](6) '../n:Count',
EggsGraded [FLOAT] '../../../n:Supply/n:Operation/n:AmountOfEggs',
EDescription [VarChar](30) '../n:Description',
EWeight [VarChar](30) '../n:Weight',
MinWeight [VarChar](30) '../n:WeightLimit/n:MinWeight',
MaxWeight [VarChar](30) '../n:WeightLimit/n:MaxWeight',
ExtraInfo1 [VarChar](20) '../../../n:Supply/n:Extra/n:Info1',
ExtraInfo2 [VarChar](20) '../../../n:Supply/n:Extra/n:Info2',
ExtraInfo3 [VarChar](20) '../../../n:Supply/n:Extra/n:Info3',
ExtraInfo4 [VarChar](20) '../../../n:Supply/n:Extra/n:Info4',
ExtraInfo5 [VarChar](20) '../../../n:Supply/n:Extra/n:Info5',
ShedNo [VarChar](6) '../../../n:Supply/n:Egg/n:ShedNumber',
ShedType [VarChar](30) '../../../n:Supply/n:Egg/n:Type',
LayDate [VarChar](12) '../../../n:Supply/n:Dates/n:LayDate',
SupplyDate [VarChar](12) '../../../n:Supply/n:Dates/n:SupplyDate',
BestBefore [VarChar](12) '../../../n:Product/n:Dates/n:BestBeforeDate'
)
EXEC sp_xml_removedocument @hDoc
User contributions licensed under CC BY-SA 3.0