OpenXML Cant get namespaces to work, possible original XML file issue?

0

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
sql-server
xml
asked on Stack Overflow Jun 16, 2017 by Jason Gillam • edited Jun 16, 2017 by Jason Gillam

2 Answers

0

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.

answered on Stack Overflow Jun 16, 2017 by Richard Hansell • edited Jun 20, 2017 by Richard Hansell
0

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
answered on Stack Overflow Jun 27, 2017 by Jason Gillam

User contributions licensed under CC BY-SA 3.0