February 10, 2011 at 3:01 am
We are performing automated testing and am writing the fluke generated result data files to a MS SQL server 2008 xml data field. The typical results file given below, i can successfully query and return the data to be opened again by the fluke ANSUR engine for review.
However, I have been given the project to query the database xml files and extract xml field data and attributes accross all different device testing results stored (of wich there is some variation on the file contents) and not having much success.
could i get some assistance on how one might extract say the serial number filed from this data file in a sql query.
any good simple explanations or reference starting point documents for a newby would be appreciated.
many thanks.
Greg
***************************************************************************
<?xml version="1.0" encoding="iso8859-1"?>
<METRONFile Type="Record" Version="2.0.1">
<Application Name="ansur" Version="2.6.0">
<Kernel Name="CHiddenKernel" Version="2.3.0"/>
<Interface Name="XML Parser" Version="2.0.1"/>
<PlugIn Name="AVPI" Version="2.4.5"/>
<PlugIn Name="ESA620" Version="1.0.3"/>
</Application>
<Template Name="ST1.mtt">
<Properties>
<StoreRTF/>
</Properties>
<TemplateData>
<TestElement ID="2" Version="1">
<PlugIn Name="AVPI" TestID="200"/>
<Info>
<Type>Checklist</Type>
<Name>Checklist</Name>
<Procedure Format="1">
<![CDATA[{\rtf1\fbidis\ansi\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 MS Sans Serif;}}
{\stylesheet{ Normal;}{\s1 heading 1;}{\s2 heading 2;}}
\viewkind4\uc1\pard\ltrpar\keepn\s2\qj\tx498\tx982\tx1464\lang3081\ul\b\f0\fs20
\par
\par
\par \pard\ltrpar\qj\tx498\tx982\tx1464\ulnone\b0
\par \pard\ltrpar\qj\b 1.\b0 \b GENERAL INSPECTION:\b0
\par \pard{\pntext\f0 1\tab}{\*\pn\pnlvlbody\pnf0\pnindent0\pnstart1\pndec }
\ltrpar\fi-283\li720\qj Check that the equipment and associated cord is free from obvious external damage.
\par {\pntext\f0 2\tab}Check that flexible cords are effectively anchored to equipment and plugs.
\par {\pntext\f0 3\tab}Check that covers, guards, and the like are secured.
\par {\pntext\f0 4\tab}Check that ventilation inlets and exhausts are unobstructed.
\par \pard\ltrpar\li437\qj
\par \pard\ltrpar\lang1033\f1\fs20
\par }
]]>
</Procedure>
</Info>
<Filter>
<IncludeInReport/>
</Filter>
<ExpectedResult>
<Standard AlphaName="Ansur" CompleteName="User defined"/>
</ExpectedResult>
<PlugInData PlugIn="AVPI">
<TestGuideSettings>
<EnableBtn Skip="True" NA="True"/>
</TestGuideSettings>
<Steps>
<Options>
<Option ID="1">Pass</Option>
<Option ID="2">Fail</Option>
</Options>
<Step Key="S1" Type="1" Required="True">
<Option ID="1" Status="Pass"/>
<Option ID="2" Status="Fail"/>
</Step>
</Steps>
</PlugInData>
</TestElement>
<TestElement ID="7">
<PlugIn Name="ESA620" TestID="100"/>
<Info>
<Type>Auto Sequence</Type>
<Name>Auto Sequence</Name>
</Info>
<Filter>
<IncludeInReport/>
</Filter>
<ExpectedResult>
<Standard AlphaName="Ansur" CompleteName="User defined">
<Limit Key="::ST"/>
</Standard>
</ExpectedResult>
<PlugInData PlugIn="ESA620">
<ValueFilter>None</ValueFilter>
<EnableBtn Skip="True" NA="True" Step="True"/>
<ModuleOptions Sep="False" Exp="False" Stop="False"/>
<TestOptions>
<Run Opt="True" HaltOnFail="True" MultiPE="False" TotalLeakage="False" MeasType="0" TestVoltage="0" AppliedParts="0"/>
<StopOnPC Before="False" After="False"/>
<Delay On="2" Off="0"/>
</TestOptions>
</PlugInData>
<TestElement ID="3">
<PlugIn Name="ESA620" TestID="400"/>
<Info>
<Type>Protective Earth Resistance</Type>
<Name>Protective Earth Resistance</Name>
<Procedure Format="1">
<![CDATA[{\rtf1\fbidis\ansi\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\froman\fprq2\fcharset2 Symbol;}{\f2\fnil\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\ltrpar\qj\lang3081\b\f0\fs20 2. PROTECTIVE EARTHING:\b0 Using an electrical safety analyser, measure the resistance between any accessible metal parts and the earth pin.
\par
\par \pard\ltrpar\li360 (Ref: AS/NZS 3760:2003)
\par Criteria:<1.0\f1 W \f0
\par \pard\ltrpar\lang1033\f2\fs20
\par }
]]>
</Procedure>
</Info>
<Filter>
<IncludeInReport/>
</Filter>
<ExpectedResult>
<Standard AlphaName="Ansur" CompleteName="User defined">
<Limit Key="::ST">
<Unit>Ohm</Unit>
<Value Type="High">1</Value>
</Limit>
</Standard>
</ExpectedResult>
<PlugInData PlugIn="ESA620">
<EnableBtn Skip="True" NA="True" Step="True"/>
<ResOptions MeasType="0" ResWires="0" PECurrent="0"/>
</PlugInData>
</TestElement>
<TestElement ID="4">
<PlugIn Name="ESA620" TestID="1010"/>
<Info>
<Type>Insulation Resistance</Type>
<Parameter>Mains to Protective Earth</Parameter>
<Name>Mains to Protective Earth</Name>
<Procedure Format="1">
<![CDATA[{\rtf1\fbidis\ansi\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\froman\fprq2\fcharset2 Symbol;}{\f2\fnil\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\ltrpar\qj\lang3081\b\f0\fs20 3. INSULATION RESISTANCE:\b0 Using an electrical safety analyser, or megohm meter set at 500V DC, measure the insulation resistance between:
\par \pard{\pntext\f0 1\tab}{\*\pn\pnlvlbody\pnf0\pnindent0\pnstart1\pndec }
\ltrpar\fi-360\li435\qj\tx435 short circuited active and neutral pins of the mains plug to the protective earth pin of the mains plug.
\par {\pntext\f0 2\tab}Short circuited active and neutral pins of the mains plug to exposed conductive parts of enclosures and accessories.
\par \pard\ltrpar\qj
\par \pard\ltrpar\li360 (Ref: AS/NZS 3760:2003)
\par Criteria:>1.0M\f1 W \f0
\par \pard\ltrpar\lang1033\f2\fs20
\par }
]]>
</Procedure>
</Info>
<Filter>
<IncludeInReport/>
</Filter>
<ExpectedResult>
<Standard AlphaName="Ansur" CompleteName="User defined">
<Limit Key="::ST">
<Unit>MOhm</Unit>
<Value Type="Low">1</Value>
</Limit>
</Standard>
</ExpectedResult>
</TestElement>
<TestElement ID="5">
<PlugIn Name="ESA620" TestID="1110"/>
<Info>
<Type>Earth Leakage Current</Type>
<Parameter>Normal Condition</Parameter>
<Name>Normal Condition</Name>
<Procedure Format="1">
<![CDATA[{\rtf1\fbidis\ansi\deff0{\fonttbl{\f0\fswiss\fprq2\fcharset0 Verdana;}{\f1\fnil\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\ltrpar\qj\lang3081\b\f0\fs20 4. EARTH LEAKAGE CURRENT: \b0 Using an electrical safety analyser, measure the leakage current from a non-isolated supply at rated voltage under no fault conditions.\b
\par
\par \pard\ltrpar\li360\b0 (Ref: AS/NZS 3760:2003)
\par Criteria: \tab Class I Equipment: <5mA
\par \tab\tab Class II Equipment: <1mA
\par \pard\ltrpar\lang1033\f1\fs20
\par }
]]>
</Procedure>
</Info>
<Filter>
<IncludeInReport/>
</Filter>
<ExpectedResult>
<Standard AlphaName="Ansur" CompleteName="User defined">
<Limit Key="::ST">
<Unit>uA</Unit>
<Value Type="High">5000</Value>
</Limit>
</Standard>
</ExpectedResult>
</TestElement>
</TestElement>
</TemplateData>
</Template>
<Setup>
<Standard AlphaName="Ansur" CompleteName="User defined"/>
<DUT>
<Item Name="Serial number" Ord="1" Caption="Serial number" Required="True" Key="True"/>
<Item Name="Equipment code" Ord="2" Caption="Appliance code"/>
<Item Name="Group" Ord="3" Caption="Group"/>
<Item Name="Status" Ord="3" Caption="Status"/>
<Item Name="Manufacturer" Ord="5" Caption="Manufacturer"/>
<Item Name="Type" Ord="6" Caption="Type"/>
<Item Name="Model" Ord="7" Caption="Model"/>
<Item Name="Location" Ord="8" Caption="Location"/>
<Item Name="Address 1" Ord="9" Caption="Address 1"/>
<Item Name="Address 2" Ord="10" Caption="Address 2"/>
</DUT>
<PlugInData PlugIn="ESA620">
<ModuleSetup/>
</PlugInData>
<TestInstruments>
<MTIData PlugIn="ESA620" ID="1">
<Type>ESA 620</Type>
<SerialNumber>9869029</SerialNumber>
<Version> UI-1.17</Version>
</MTIData>
</TestInstruments>
</Setup>
<Record>
<TestData>
<Status>Passed</Status>
<Date Year="2009" Month="5" Day="14" Hour="12" Min="33" Sec="3"/>
</TestData>
<ResultData>
<ResultItem ElementID="2" Version="1">
<Status>Passed</Status>
<Measurement Key="S1">
<Description></Description>
<Value Type="Text">Pass</Value>
<Status>Passed</Status>
<StepType>1</StepType>
</Measurement>
</ResultItem>
<ResultItem ElementID="7">
<Status>Passed</Status>
<ResultItem MTI="1" ElementID="3">
<Status>Passed</Status>
<Measurement Key="::ST">
<Description>Protective Earth Resistance</Description>
<Value Type="Float">.279</Value>
<Unit>Ohm</Unit>
<Status>Passed</Status>
</Measurement>
</ResultItem>
<ResultItem MTI="1" ElementID="4">
<Status>Passed</Status>
<Measurement Key="::ST">
<Description>Mains to Protective Earth</Description>
<Value Type="Float">77.4</Value>
<Unit>MOhm</Unit>
<Status>Passed</Status>
</Measurement>
</ResultItem>
<ResultItem MTI="1" ElementID="5">
<Status>Passed</Status>
<Measurement Key="::ST">
<Description>Normal Condition</Description>
<Value Type="Float">31</Value>
<Unit>uA</Unit>
<Status>Passed</Status>
</Measurement>
</ResultItem>
</ResultItem>
</ResultData>
</Record>
</METRONFile>
February 10, 2011 at 3:49 am
just tried your suggestion and has returned the error message
"Must declare the scalar variable @val"
i have been doing lots of reading on the MS forum and google searches and ether getting retured empty data or an assortment of errors like as above... im sure its my syntax combined with the current knowledge - as i am still trying to grasp the xml and sql 2008 symantics....
unfortunately lots of trial and error.....the knowledged and understanding has not quite come together yet!
February 10, 2011 at 6:06 pm
The calling program (LABVIEW) is linked through OLEDB. we are only wanting the return of the ascii values and, if possible, not through the creation of a second table?
February 13, 2011 at 11:03 pm
Eureka - ok got information being sent back. Thankyou for your input the knowledge has fallen into place somewhat.
my next question is that fluke ansur system manager in the xml code has nested "\resultitem" within the nodes structure (upto 3 levels worst case), is there some way to create a dynamic traversal of all these nested levels to return the data description and values and status?
Greg
February 14, 2011 at 6:38 pm
Thankyou for your input that was a Big help and very much appreciated....probably a few more questions a little later:-)
February 16, 2011 at 8:54 pm
we have normalised some xml files from our automated fluke testing system by separating the sections into two tables with fields set to store as xml. one table holding results and the other storing single copies of the proceedures. For recall and display of the results the testing system requires concatination of the two tables xml feild data. Help on A sql statement to merge the two xml files back together is sought.
The output file needs to be the combination of the proceedure file with the xml container results file inserted between the </template> and the </METRONFile> as in the provided complete example file at the beginning of the post.
i.e anything above the </template> to the top of the file is a proceedure
anything in the <setup>, <dut> etc is the results container
resolving would sought out a major sql hurdle in this project
Thanks
February 17, 2011 at 2:55 am
I have been trying to comphrend the syntax, the concept of variables i understand
i have created the two simplified tables defined by the below sql statement.
the unque test id filename identifies a particular devices acceptance testing,
In each row of the procedure table, an individual single copy xml of a complete procedures is stored (i.e the procedures are comon to a particular device type and is removed as it is a common repeating section in the results report file).
Each row in the results table is represents a different test and the extracted device under test etc. complete file is stored.
the concaninated xml reports from both tables linked by the template name
As stated earlier, the proceedure file is the beginning of the report and the results line contains all the remainder information in the xml field to be inserted in between </template> and the </Metron>
Unfortunately i am not succeding with endless reported errors - please advise:(
CREATE TABLE [dbo].[Proceedure](
[template_name] [varchar](255) NOT NULL,
[Procedure] [xml] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TestResults](
[id_test_filename] [varchar](35) NOT NULL,
[template_name] [varchar](255) NOT NULL,
[Results] [xml] NOT NULL
) ON [PRIMARY]
GO
February 17, 2011 at 6:58 pm
there is the rare occasion a service person or an auditor needs to recall a previously run test as a verification or quality measure and review the results through the fluke review engine where we need to reconstruct the composite file in order for the system to display test and results. so generally returning only one complete xml test record is required. ie the complete example file above which is the procedure (mtt part) and the results (mtr) part from the respective tables and the results reinserted back into the end of the procedure.
The current setup is, the results record is extracted and stored in the results table as a whole block cut from the procedure. If the procedure is a new one (not defined in the procedure table) designated by the given template name it is then stored in the procedure table as a procedure that can be subsequently recalled and rerun in future with a new test_filename id. in this case the xml contanier of results is just stored as a new line in the results table
This project task is supposed to be a basic system for recall at the point of service. stored results are scheduled processed that report via web published pages on results for QA purposes and mangers.
February 17, 2011 at 11:58 pm
Thanking you for time and input it has been most helpfull.
i will now examine closely & work out how this all fits together.
Your contribution is appreciated and valued.
It appears there is a lot of intrest in this xml topic but not many contributing. Thankyou for going that extra distance and helping me with this project assignment
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply