elements with and wothout namespaces

• Matjaz Justin

  Mr or Mrs. 500

  Points: 568

  More actions

  July 1, 2010 at 12:21 am

  #98765

  Hello. I'm lost in a jungle of different options how to extract data from xml. I have a combination of elements with namespace and without namespace. Below is example.

  DECLARE @x XML

  SELECT @x = '

  <cd:Document xmlns="http://www.zbs-giz.si/Schemas/2006/ZBSxml/2.0" xmlns:cd="http://www.crea.si/Schemas/2004/Document/ZBSxml/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.crea.si/Schemas/2004/Document/ZBSxml/2.0 ..\ZbsCreaDoc.xsd">

  <cd:Data>

  <cd:DataFormat>

  <cd:MimeType>text/xml</cd:MimeType>

  </cd:DataFormat>

  <cd:Content>

  <cd:EmbeddedData>

  <Paket>

  <VodilniZapis>

  <StevilkaRacunaNalogodajalca>SI56020100090669843</StevilkaRacunaNalogodajalca>

  <StevilkaPaketa>000001</StevilkaPaketa>

  <DatumPaketa>2006-03-08</DatumPaketa>

  <NacinObdelave>SNGL</NacinObdelave>

  </VodilniZapis>

  <StandardniNalogiVEvrih>

  <StandardniNalogVEvrih>

  <ReferencnaOznakaNalogaNalogodajalca>SI0012345-089234</ReferencnaOznakaNalogaNalogodajalca>

  <Prioriteta>51</Prioriteta>

  <StevilkaRacunaNalogodajalca>SI56020100090669843</StevilkaRacunaNalogodajalca>

  <Nalogodajalec>

  <Naziv>Crea d.o.o.</Naziv>

  <Naslov>Gospodinjska 8</Naslov>

  <Naslov1>1000 Ljubljana</Naslov1>

  <Drzava>SI</Drzava>

  </Nalogodajalec>

  <IdentifikacijaNalogodajalca>

  <PravnaOseba>

  <DavcnaStevilka>83782796</DavcnaStevilka>

  </PravnaOseba>

  </IdentifikacijaNalogodajalca>

  <StevilkaRacunaPrejemnika>SI5603100001087772844</StevilkaRacunaPrejemnika>

  <Prejemnik>

  <Naziv>Crea d.o.o.</Naziv>

  <Naslov>Tivolska 50</Naslov>

  <Naslov1>1000 Ljubljana</Naslov1>

  <Drzava>SI</Drzava>

  <KodaSWIFT>LJBASI2X</KodaSWIFT>

  </Prejemnik>

  <IdentifikacijaPrejemnika>

  <PravnaOseba>

  <DavcnaStevilka>83782796</DavcnaStevilka>

  </PravnaOseba>

  </IdentifikacijaPrejemnika>

  <VrstaPlacilnegaInstrumenta>SEPA</VrstaPlacilnegaInstrumenta>

  <PodatkiONakazilu>

  <StrukturiranaReferenca>

  <Referenca>002000000143</Referenca>

  <DodatenOpisNamena>PLACILO</DodatenOpisNamena>

  </StrukturiranaReferenca>

  </PodatkiONakazilu>

  <ZnesekPlacila>

  <Znesek>93.60</Znesek>

  <OznakaValute>EUR</OznakaValute>

  </ZnesekPlacila>

  <StatisticnoPorocanje>

  <VrstaPosla>A3011</VrstaPosla>

  </StatisticnoPorocanje>

  <DatumObdelave>2006-03-08</DatumObdelave>

  <KodaEksterna>ACCT</KodaEksterna>

  <KategorijaNamena>SG01</KategorijaNamena>

  <VrstaSporocila>0</VrstaSporocila>

  </StandardniNalogVEvrih>

  </StandardniNalogiVEvrih>

  </Paket>

  </cd:EmbeddedData>

  </cd:Content>

  </cd:Data>

  </cd:Document>'

  -- Script below does not work.

  ;WITH XMLNAMESPACES('http://www.crea.si/Schemas/2004/Document/ZBSxml/2.0' as cd)

  select x.Paket.value('@StevilkaRacunaNalogodajalca[1]', 'varchar(34)') AS StevilkaRacunaNalogodajalca

  --select x.Paket.query('.')

  from @x.nodes('//cd:Document/cd:Data/cd:Content/cd:EmbeddedData/Paket/VodilniZapis') AS x(Paket)

• matthew.galaviz

  Grasshopper

  Points: 12

  More actions

  August 8, 2010 at 8:24 am

  #1203932

  this worked for me.

  ;WITH XMLNAMESPACES('http://www.crea.si/Schemas/2004/Document/ZBSxml/2.0' as cd, 'http://www.zbs-giz.si/Schemas/2006/ZBSxml/2.0' as a)

  select x.Paket.value('a:StevilkaRacunaNalogodajalca[1]', 'varchar(34)') AS StevilkaRacunaNalogodajalca

  --select x.Paket.query('.')

  from @x.nodes('//cd:Document/cd:Data/cd:Content/cd:EmbeddedData/a:Paket/a:VodilniZapis') AS x(Paket)

• Matjaz Justin

  Mr or Mrs. 500

  Points: 568

  More actions

  August 9, 2010 at 1:07 am

  #1204001

  I managed with this:

  ;WITH XMLNAMESPACES('http://www.crea.si/Schemas/2004/Document/ZBSxml/2.0' as cd, DEFAULT 'http://www.zbs-giz.si/Schemas/2006/ZBSxml/2.0')

  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

  --select x.Paket.query('.')

  from @p_XML.nodes('//cd:Document/cd:Data/cd:Content/cd:EmbeddedData/Paket/VodilniZapis') AS x(Paket)

• grovelli-262555

  SSCrazy

  Points: 2633

  More actions

  August 10, 2010 at 5:26 am

  #1204660

  Where do the extracted data end up?

• Matjaz Justin

  Mr or Mrs. 500

  Points: 568

  More actions

  August 11, 2010 at 11:24 pm

  #1205807

  I'm using extracted data for preview.