X-Received: by 10.42.196.71 with SMTP id ef7mr5769047icb.24.1392027642264; Mon, 10 Feb 2014 02:20:42 -0800 (PST)
X-Received: by 10.182.213.7 with SMTP id no7mr208122obc.19.1392027642107; Mon, 10 Feb 2014 02:20:42 -0800 (PST)
Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!c10no15068337igq.0!news-out.google.com!vg8ni22igb.0!nntp.google.com!c10no15068331igq.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Mon, 10 Feb 2014 02:20:41 -0800 (PST)
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=212.103.209.102; posting-account=kSgJqgoAAAApszHWdDS4TQ5wAOg42y_H
NNTP-Posting-Host: 212.103.209.102
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <0a2e63e1-d6c0-4493-b52b-2d60a50bf1b7@googlegroups.com>
Subject: Get XML values from nodes and child nodes
From: giancarlo.francesconi@gmail.com
Injection-Date: Mon, 10 Feb 2014 10:20:42 +0000
Content-Type: text/plain; charset=ISO-8859-1
Xref: csiph.com comp.databases.ms-sqlserver:1664

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...)