Groups > comp.databases.ms-sqlserver > #1717

Re: Get XML values from nodes and child nodes

X-Received	by 10.236.5.133 with SMTP id 5mr8355289yhl.33.1394540930859; Tue, 11 Mar 2014 05:28:50 -0700 (PDT)
X-Received	by 10.182.17.67 with SMTP id m3mr11308obd.23.1394540930754; Tue, 11 Mar 2014 05:28:50 -0700 (PDT)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!hw13no3423773qab.1!news-out.google.com!gi6ni106igc.0!nntp.google.com!ur14no5639903igb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Tue, 11 Mar 2014 05:28:50 -0700 (PDT)
In-Reply-To	<0a2e63e1-d6c0-4493-b52b-2d60a50bf1b7@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=125.19.34.1; posting-account=RN4IDQoAAAAZzCpG5hUzr2OsJG3dlDNS
NNTP-Posting-Host	125.19.34.1
References	<0a2e63e1-d6c0-4493-b52b-2d60a50bf1b7@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<fa639451-b030-4e2e-ad2b-4d8c9976512c@googlegroups.com> (permalink)
Subject	Re: Get XML values from nodes and child nodes
From	Bal Govind <balgovindjss@gmail.com>
Injection-Date	Tue, 11 Mar 2014 12:28:50 +0000
Content-Type	text/plain; charset=ISO-8859-1
X-Received-Bytes	2896
X-Received-Body-CRC	3595322606
Xref	csiph.com comp.databases.ms-sqlserver:1717

Show key headers only | View raw

On Monday, February 10, 2014 3:50:41 PM UTC+5:30, giancarlo....@gmail.com wrote:
> Hi everybody. My XML file is:
> 
> <Locatore>
> 
>   <NumeroProgressivo>001</NumeroProgressivo>
> 
>   <CodiceFiscale>CSTNDA69P90H523R</CodiceFiscale>
> 
>   <PersoneFisiche>
> 
> 	<Cognome>CAST</Cognome>
> 
> 	<Nome>NADIR</Nome>
> 
> 	<Sesso>F</Sesso>
> 
> 	<DataNascita>10091979</DataNascita>
> 
> 	<ComuneNascita>RONCA</ComuneNascita>
> 
> 	<ProvinciaNascita>BL</ProvinciaNascita>
> 
>   </PersoneFisiche>
> 
> </Locatore>
> 
> <Locatore>
> 
>   <NumeroProgressivo>002</NumeroProgressivo>
> 
>   <CodiceFiscale>PRSGRI74L29F443L</CodiceFiscale>
> 
>   <PersoneFisiche>
> 
> 	<Cognome>PERISSI</Cognome>
> 
> 	<Nome>IGOR</Nome>
> 
> 	<Sesso>M</Sesso>
> 
> 	<DataNascita>29071970</DataNascita>
> 
> 	<ComuneNascita>MONTE</ComuneNascita>
> 
> 	<ProvinciaNascita>SA</ProvinciaNascita>
> 
>   </PersoneFisiche>
> 
> </Locatore>
> 
> 
> 
> I need to get in ONE RECORD, both node value and his child nodes values, like this
> 
> 001 | CSTNDA69P90H523R | CAST    | NADIR | F | 10091979 | RONCA | BL
> 
> 002 | PRSGRI74L29F443L | PERISSI | IGOR  | M | 29071970 | MONTE | SA
> 
> ....
> 
> 
> 
> With this: 
> 
> SELECT 
> 
> X.valore.query('NumeroProgressivo').value('.', 'VARCHAR(20)') as NumeroProgressivo,
> 
> X.valore.query('CodiceFiscale').value('.', 'VARCHAR(16)') as CodiceFiscale
> 
> 
> 
> FROM Tbulk 
> 
> CROSS APPLY Tbulk.nodes('Fornitura/Documento/Soggetti/PrimoModulo/Locatore') AS X(valore);
> 
> 
> 
> I only get the first node values (NumeroProgressivo, CodiceFiscale), but not the child nodes (cognome, nome sesso...)


You can do it using OpenXML query that is an easy approach i think :)

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Get XML values from nodes and child nodes giancarlo.francesconi@gmail.com - 2014-02-10 02:20 -0800
  Re: Get XML values from nodes and child nodes Erland Sommarskog <esquel@sommarskog.se> - 2014-02-10 23:45 +0100
  Re: Get XML values from nodes and child nodes Bal Govind <balgovindjss@gmail.com> - 2014-03-11 05:28 -0700

csiph-web