December 10, 2011 at 2:27 pm
Hi
I'm trying to extract data out of an XML, and by and large am unable to get most of the data. It appears that the XML has two namespaces, and I only seem to be able to get the data out of one of them. I've new to XML and have trialled and errored a few things, but for the life of me cannot get the value of one of the elements.
This is the XML:
<s:ISMessage xmlns="http://informationsystems/schema/messages" xmlns:s="http://informationsystems/schema/messages">
<s:Source>AuthoritativePublisher</s:Source>
<s:DateAndTime>2011-11-16T08:28:39.247Z</s:DateAndTime>
<s:ClassID>11005</s:ClassID>
<s:Version>1</s:Version>
<s:ClassDescription>Dumpstation 1 Unloading Task Commenced</s:ClassDescription>
<s:Unloading>
<s:UnloadingTaskCommenced>
<TrainCycleID>43631</TrainCycleID>
<Job>134178</Job>
<s:UnloadingEvent>
<s:Events xmlns:s="http://controlsystems/schema/Events">
<s:LogixEvent xmlns:s="http://controlsystems/schema/Events">
<s:Source>PLC_ASU</s:Source>
<s:DateAndTime>2011-11-16T08:28:39.246Z</s:DateAndTime>
<s:ClassID>11005</s:ClassID>
<s:Version>1</s:Version>
<s:UnloadingEvent>
<s:UnloadingOperatorAction>
<s:StartTrain>
<s:DumpStation>1</s:DumpStation>
<s:Destination>1011</s:Destination>
<s:SelectionID>15330</s:SelectionID>
<s:TaskID>205834</s:TaskID>
<s:WagonsProcessed>0</s:WagonsProcessed>
<s:TonnesProcessed>46</s:TonnesProcessed>
</s:StartTrain>
</s:UnloadingOperatorAction>
</s:UnloadingEvent>
</s:LogixEvent>
</s:Events>
</s:UnloadingEvent>
</s:UnloadingTaskCommenced>
</s:Unloading>
</s:ISMessage>
I can get the TrainCycleID and JobID details fine, but I also need to get the DumpStation, Destination, SelectionID, etc, and it seems the "s:" in the elements is confusing me. Anyone able to help?
This is the query that I'm using. What am I missing ?
WITH XMLNAMESPACES(
'http://informationsystems/schema/messages' as m,
'http://controlsystems/schema/Events' as s,
default 'http://informationsystems/schema/messages'
)
INSERT INTO @JourneyDetails( TrainCycleID , JobID, EventDateAndTimeZulu, Dumpstation, Destination)
SELECT
x.value('(TrainCycleID)[1]','int') AS TrainCycleID,
x.value('(Job)[1]','int') AS JobID,
x.value('(s:Events/s:LogixEvent/s:DateAndTime)[1]','varchar(max)') AS EventDateAndTimeZulu,
x.value('(s:Events/s:LogixEvent/s:UnloadingEvent/s:UnloadingOperatorAction/s:StartTrain/s:DumpStation)[1]','varchar(max)') AS Dumpstation,
x.value('(s:Events/s:LogixEvent/s:UnloadingEvent/s:UnloadingOperatorAction/s:StartTrain/s:Destination)[1]','varchar(max)') AS Destination,
FROM @xml.nodes('//UnloadingTaskCommenced') v(x)
December 22, 2011 at 12:43 am
I have the same problem.
declare @x xml
set @x =
'<Document>
<Data>
<DataFormat>
<MimeType>text/xml</MimeType>
</DataFormat>
<Content>
<EmbeddedData>
<Paket xmlns="http://www.zbs-giz.si/Schemas/2006/ZBSxml/2.2">
<VodilniZapis>
<StevilkaRacunaNalogodajalca>SI56101000000055382</StevilkaRacunaNalogodajalca>
<StevilkaPaketa>000000</StevilkaPaketa>
<DatumPaketa>2011-12-09</DatumPaketa>
<NacinObdelave>SNGL</NacinObdelave>
</VodilniZapis>
<Izpiski>
<Izpisek>
<GlavaIzpiska>
<StevilkaIzpiska>00265</StevilkaIzpiska>
<KodaSWIFT>BAKOSI2XXXX</KodaSWIFT>
<StevilkaRacunaNalogodajalca>SI56101000000055382</StevilkaRacunaNalogodajalca>
<OznakaValute>EUR</OznakaValute>
<ZnesekZacetnoStanje>442111.99</ZnesekZacetnoStanje>
<ZnesekKoncnoStanje>207193.74</ZnesekKoncnoStanje>
<SkupajVBreme>
<Znesek>273214.78</Znesek>
<SteviloTransakcij>000083</SteviloTransakcij>
</SkupajVBreme>
<SkupajVDobro>
<Znesek>38296.53</Znesek>
<SteviloTransakcij>000559</SteviloTransakcij>
</SkupajVDobro>
<DatumZacetkaObdobja>2011-12-09</DatumZacetkaObdobja>
<DatumKoncaObdobja>2011-12-09</DatumKoncaObdobja>
<DatumPredhodnegaIzpiska>2011-12-08</DatumPredhodnegaIzpiska>
</GlavaIzpiska>
<Promet>
<PostavkaPrometa>
<ReferencnaOznakaNalogaNalogodajalca>SI99</ReferencnaOznakaNalogaNalogodajalca>
<ReferencnaOznakaNalogaBanke>1111241111825152</ReferencnaOznakaNalogaBanke>
<PodatkiONalogodajalcu>
<Nalogodajalec>
<Naziv>ROJC IGOR S.P.</Naziv>
<Naslov>KUBED 66A</Naslov>
<Naslov1></Naslov1>
<Drzava>SI</Drzava>
<StevilkaRacuna>SI56101000045915721</StevilkaRacuna>
</Nalogodajalec>
</PodatkiONalogodajalcu>
<PodatkiONakazilu>
<StrukturiranaReferenca>
<Referenca>SI125000113919647</Referenca>
<DodatenOpisNamena>SUPP-Pla?ilo ra?una št. 11391965</DodatenOpisNamena>
</StrukturiranaReferenca>
</PodatkiONakazilu>
<ZnesekPlacila>
<Znesek>57.1</Znesek>
<OznakaValute>EUR</OznakaValute>
</ZnesekPlacila>
<IndikatorKnjizbe>2</IndikatorKnjizbe>
<KodaEksterna>SUPP</KodaEksterna>
<DatumValute>2011-12-09</DatumValute>
<DatumKnjizenja>2011-12-09</DatumKnjizenja>
</PostavkaPrometa>
</Promet>
</Izpisek>
</Izpiski>
</Paket>
</EmbeddedData>
</Content>
</Data>
</Document>'
-- select @x
selectx.Paket.value('StevilkaRacunaNalogodajalca[1]', 'varchar(34)') AS StevilkaRacunaNalogodajalca
,x.Paket.value('DatumPaketa[1]', 'datetime') AS DatumPaketa
,x.Paket.value('StevilkaPaketa[1]', 'smallint') AS StevilkaPaketa
from @x.nodes('//Document/Data/Content/EmbeddedData/Paket/VodilniZapis') AS x(Paket)
Query above does not return anything. But if I change node Paket
<Paket xmlns="http://www.zbs-giz.si/Schemas/2006/ZBSxml/2.2">
to
<Paket>
everything is ok!. What I do not know?
January 5, 2012 at 7:45 am
Here is a slightly revised query that should work with the example xml:
WITH XMLNAMESPACES(
'http://informationsystems/schema/messages' as s,
'http://controlsystems/schema/Events' as m,
default 'http://informationsystems/schema/messages'
)
SELECT
x.value('(TrainCycleID)[1]','int') AS TrainCycleID,
x.value('(Job)[1]','int') AS JobID,
x.value('(s:UnloadingEvent/m:Events/m:LogixEvent/m:DateAndTime)[1]','varchar(max)') AS EventDateAndTimeZulu,
x.value('(s:UnloadingEvent/m:Events/m:LogixEvent/m:UnloadingEvent/m:UnloadingOperatorAction/m:StartTrain/m:DumpStation)[1]','varchar(max)') AS Dumpstation,
x.value('(s:UnloadingEvent/m:Events/m:LogixEvent/m:UnloadingEvent/m:UnloadingOperatorAction/m:StartTrain/m:Destination)[1]','varchar(max)') AS Destination
FROM @xml.nodes('//UnloadingTaskCommenced') v(x)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply