June 14, 2010 at 4:44 am
Hello every body!
I have a table called "Controles" wich has a XML column called "Rcontenu".
here a sample XML file:
<envoi xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" AgrementCVT="xxxxx" NumeroOrdre="0" DateHeureEnvoi="2009-02-09T07:53:57.043" LigneNumero="1">
<Controle NumeroPV="0000" AgentCIN="00000">
<TypeControle>VT</TypeControle>
<PreviousNegativeControl xsi:nil="true" />
<DateHeureDebutControle>2009-02-09T07:42:51.577</DateHeureDebutControle>
<DateHeureFinControle>2009-02-09T07:44:30.843</DateHeureFinControle>
<PVDateHeure>2009-02-09T07:53:23.607</PVDateHeure>
<LotNumero>xxxxx</LotNumero>
<ControleResult>VOK</ControleResult>
<vehicule MatriculeZone="1" MatriculeLetter="A" MatriculeSerial="0000" ChassisType="0000" ChassisSerial="xxxxx">
<ImmatriculationDate>1992-03-10</ImmatriculationDate>
<MiseEnCirculationDate>1981-12-04</MiseEnCirculationDate>
<ImprimeSN>0000</ImprimeSN>
<MutationDate>2003-05-09</MutationDate>
<NombreCylindre>4</NombreCylindre>
<Marque>xxxxx</Marque>
<Genre>11</Genre>
<GenreReel>11</GenreReel>
<Carburant>DI</Carburant>
<Nom>xxxxx</Nom>
<Prenom>xxxxx</Prenom>
</vehicule>
<pneus count="4">
<pneu code="1" Marque="xxxxx" refSection="70" refSerie="24" refStruct="R" refDiametre="70" refCharge="20" refSerial="0245" />
<pneu code="2" Marque="xxxxx" refSection="41" refSerie="2" refStruct="R" refDiametre="70" refCharge="20" refSerial="02455" />
</pneus>
<mesures>
<essieux count="1">
<essieu code="0">
<EffiG xsi:nil="true" />
<EffiD xsi:nil="true" />
<FreinSVCDesequi xsi:nil="true" />
<SuspensionDissymetry xsi:nil="true" />
</essieu>
</essieux>
<Pollution1>0.00</Pollution1>
<PollutionCalc>2.00</PollutionCalc>
<PolCORalentiAccelere xsi:nil="true" />
</mesures>
<constats>
<constat code="0.1.1.1.1." res="0" />
<constat code="0.1.1.2.1." res="1" />
<constat code="0.1.1.3.1." res="0" />
<constat code="0.2.1.1.1." res="-1" />
<constat code="0.2.1.1.2." res="0" />
<constat code="0.2.1.2.1." res="0" />
<constat code="0.2.1.2.2." res="1" />
<constat code="0.2.1.2.3." res="0" />
<constat code="0.2.2.1.1." res="0" />
<constat code="1.1.1.1.1." res="0" />
<constat code="1.1.1.1.2." res="0" />
<constat code="1.1.1.1.3." res="1" />
<constat code="1.1.1.2.1." res="0" />
<constat code="1.1.2.1.1." res="1" />
<constat code="1.1.2.1.2." res="0" />
<constat code="2.1.1.1.2." res="2" />
<constat code="2.2.1.1.1." res="0" />
<constat code="2.2.1.2.1." res="2" />
<constat code="2.2.2.1.1." res="0" />
<constat code="2.2.2.1.2." res="0" />
<constat code="2.2.2.2.1." res="0" />
<constat code="2.3.1.1.1." res="0" />
<constat code="3.1.1.1.1." res="-1" />
<constat code="3.1.1.1.2." res="0" />
<constat code="3.1.1.1.3." res="0" />
<constat code="3.1.1.2.1." res="1" />
<constat code="3.1.1.3.1." res="0" />
<constat code="3.1.2.1.1." res="0" />
<constat code="3.1.2.2.1." res="0" />
<constat code="4.2.1.1.1." res="0" />
<constat code="4.2.1.1.2." res="0" />
<constat code="4.2.1.2.1." res="0" />
<constat code="4.2.1.2.2." res="0" />
<constat code="4.2.1.3.1." res="1" />
<constat code="4.2.1.3.2." res="0" />
<constat code="4.2.1.5.1." res="0" />
<constat code="5.2.2.2.1." res="0" />
<constat code="5.3.1.1.1." res="0" />
<constat code="5.3.1.1.2." res="1" />
<constat code="5.3.1.1.3." res="0" />
<constat code="5.3.1.1.4." res="1" />
<constat code="5.3.1.2.1." res="0" />
<constat code="5.3.1.3.1." res="0" />
<constat code="5.3.1.4.1." res="0" />
<constat code="6.1.1.1.1." res="0" />
<constat code="6.1.1.1.2." res="-1" />
<constat code="6.1.1.1.3." res="0" />
<constat code="6.1.1.1.4." res="0" />
<constat code="6.1.1.1.5." res="1" />
<constat code="6.1.1.1.6." res="0" />
<constat code="6.1.1.1.7." res="0" />
<constat code="6.1.1.2.1." res="2" />
</constats>
</Controle>
</envoi>
I want to extract the codes of the constats (in "constats" element) where the result "res=1" from all the files in my table "controles"!
So Can any body help me please! it's urgent! 🙂
June 14, 2010 at 5:18 am
Hi there
Well it seems that you need to study some XML stuff
Check this link as well
As far as your query , i have just done a test and here's the code.
declare @XMLOrderLineData varchar(8000)
set @XMLOrderLineData = '' ---your xml sample data here
declare @xml_hndl int
exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLOrderLineData
SELECT count(*)
FROM OPENXML (@xml_hndl, '/envoi/Controle/constats/constat',0)
WITH (
constat varchar(10),
resint
)
SELECT *
FROM OPENXML (@xml_hndl, '/envoi/Controle/constats/constat',0)
WITH (
code varchar(20),
resint
)
where res = 2
Here's the output
coderes
2.1.1.1.2.2
2.2.1.2.1.2
6.1.1.2.1.2
June 16, 2010 at 10:02 am
Hi funooni,
I'm sorry I couldn't reply quickly due to some testing problems :)!
The code works fine thank you very much!:D
Othmane
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply