Q: Extrapolation data from XML with Query in SQL Server I'm trying to get a series of values ​​from the XML document that I report at the bottom of the page, using xpath queries inside the DB SQL server.
I am absolutely not expert in the subject, I apologize for the inaccuracies. I managed following the instructions also had on this site and found online to carry out the following procedure (on my pc the XML file is located in my folder at the path C: \ XML-SQL \ lotto_3.xml)
DECLARE @xmlDoc xml;

/ * I load the entire XML file into an XML variable * /

SET @xmlDoc = (SELECT Q.BulkColumn

      FROM OPENROWSET (BULK 'C: \ XML-SQL \ lotto_3.xml', SINGLE_CLOB) AS Q);

 select @xmlDoc as mydoc

 / * I run the same query using the OPENXML function * /

 DECLARE @hdoc int

 EXEC sp_xml_preparedocument

@hdoc OUTPUT,

@xmlDoc,

'<root xmlns: std = "http://www.uni.com/U3011/sincro/"

xmlns: s = "http://www.uni.com/U3011/sincro/" /> '

 SELECT *

FROM OPENXML (@hdoc, '/ std: IdC / std: FileGroup / std: File [@s: format = "application / pdf"] / std: Path')

WITH (Filename nvarchar (max) '/ std: IdC / std: FileGroup / std: File [@s: format = "application / pdf"] / std: Path',
ID nvarchar (max) '/ std: IdC / std: SelfDescription / std: ID');

EXEC sp_xml_removedocument @hdoc;

which somehow allows me to read some tags from the XML in question.
The values ​​that interest me are those contained in the first TAG
<ID s: scheme = "local"> 1558224025077 </ID>

in all tag values
<Path> docs / 002D4DC0.pdf </ Path>

and those of the respective tags, belonging to the same node of every tag  docs
<m: meta value = "2019-05-06T19: 40: 11 + 02: 00"
<m: meta value = "2019-04-30"
<m: meta value = "SCOTT ITALIA S.R.L."
<m: meta value = "200009"
<m: meta value = "IT03770200164"
<m: meta value = "9102154398"

I would like to get several columns each with the data of each group, like this:
Path             Data1               Name                  VatCode       number              ID
002D4DC0.pdf     2019-04-30         SCOTT                  9102154398    IT03770200164    1558224025077
002D4DC2.pdf     2019-04-30         SCOTT                  9102154402    IT03770200164    1558224025077
etc ....

and in the ID column possibly always have the same value = 1558224025077
With the procedure I set, however, I only get, looking for the values ​​of the Tag "Path" and "ID s: scheme,  the repetition (for 1114 lines that corresponds to the number of "groupings" present in the file) of the same value present in the first Tag "Path"
Instead, I would like to show all the values ​​as in the previous example.
Is it possible to carry out this data extraction in SQL Server?
docs / 002D4DC0.pdf 1558224025077
docs / 002D4DC0.pdf 1558224025077
docs / 002D4DC0.pdf 1558224025077
docs / 002D4DC0.pdf 1558224025077`enter code here`
docs / 002D4DC0.pdf 1558224025077
docs / 002D4DC0.pdf 1558224025077
docs / 002D4DC0.pdf 1558224025077
docs / 002D4DC0.pdf 1558224025077
docs / 002D4DC0.pdf 1558224025077
docs / 002D4DC0.pdf 1558224025077

Instead, an expression that allows me to correctly extract all the values ​​of the "PAth" tag in XPath Tester applications is
//*[name()= 'Path']

while with
//meta[1]/@value

I get based on the indices 1,2 etc .. the values ​​of the "Value" tags
But in sql server i can't apply them.
I thank very much in advance who can help me.
bye
This is example of my XML (others whith same structure can be much longer)
  <?xml version="1.0" encoding="UTF-8"?>
<IdC xmlns="http://www.uni.com/U3011/sincro/" xmlns:s="http://www.uni.com/U3011/sincro/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.uni.com/U3011/sincro/ UNI1138600_2010_EIT.xsd http://andxor.it/tDoc/lotto.xsd lotto.xsd http://andxor.it/tDoc/metadata.xsd metadata.xsd">
  <SelfDescription>
    <ID s:scheme="local">1558224025077</ID>
    <CreatingApplication>
      <Name>tDoc</Name>
      <Version>r16610</Version>
      <Producer>Andxor Soluzioni Informatiche srl</Producer>
    </CreatingApplication>
    <MoreInfo s:XMLScheme="http://andxor.it/tDoc/lotto.xsd">
      <EmbeddedMetadata>
        <lotto xmlns="http://andxor.it/tDoc/lotto.xsd">
          <company>SCOTT</company>
          <doctype>FATTURE_PASSIVE</doctype>
          <period>2019</period>
          <previous id="1557187238227">60AECF9413EE8D53F2238C03D8271670C5761FC5AEE92951966D3BE31FC192E2</previous>
        </lotto>
      </EmbeddedMetadata>
    </MoreInfo>
  </SelfDescription>
  <VdC>
    <ID s:scheme="local">1558224025077</ID>
  </VdC>
  <FileGroup xmlns:m="http://andxor.it/tDoc/metadata.xsd">
    <File s:format="application/pdf">
      <ID s:scheme="local">2969024</ID>
      <Path>docs/002D4DC0.pdf</Path>
      <Hash s:function="SHA-256">CCB149AA3C63F4B7519A49E0453742FFC63E85CD44DC458BB094D0682A053E40</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-06T19:40:11+02:00"/>
            <m:meta name="Data" value="2019-04-30"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102154398"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2969026</ID>
      <Path>docs/002D4DC2.pdf</Path>
      <Hash s:function="SHA-256">8005A54C5D12219295B3E16CD28A93C3CDB9C9DA67630E20F8DAB749DADF1F56</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-06T19:40:13+02:00"/>
            <m:meta name="Data" value="2019-04-30"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102154402"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2969028</ID>
      <Path>docs/002D4DC4.pdf</Path>
      <Hash s:function="SHA-256">4B516C97B13D5FC7C4D13B27CD08C3B7238292CA19B922EACA002FC3EA5648FF</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-06T19:40:15+02:00"/>
            <m:meta name="Data" value="2019-04-30"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102154401"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2969030</ID>
      <Path>docs/002D4DC6.pdf</Path>
      <Hash s:function="SHA-256">98DFE554FFE5E915819CD65DE144F87A987B44B087FFFD2D65CFD17CD83AE54F</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-06T19:40:17+02:00"/>
            <m:meta name="Data" value="2019-04-30"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102154403"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971559</ID>
      <Path>docs/002D57A7.pdf</Path>
      <Hash s:function="SHA-256">84E6FA2DE5BC6C76FFD964676BA3A489C22CCE3437E09A11BCA162CBEA5DE1E0</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:57:15+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164873"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971561</ID>
      <Path>docs/002D57A9.pdf</Path>
      <Hash s:function="SHA-256">D597B29A835DA8D652D37E504B244B526815BD14C9DCDEF36B9CE951691E99A0</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:57:16+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164874"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971563</ID>
      <Path>docs/002D57AB.pdf</Path>
      <Hash s:function="SHA-256">1511BC54E9B5B5C23AB66C2CF7289829674674E4291B8A0B1F94BA553012F944</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:57:19+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164878"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971565</ID>
      <Path>docs/002D57AD.pdf</Path>
      <Hash s:function="SHA-256">C6A1B7437636F56A2FD944DA2DA966B47F00D5E7F42687B9F56F47166595558B</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:57:20+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164871"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971567</ID>
      <Path>docs/002D57AF.pdf</Path>
      <Hash s:function="SHA-256">855AF9E5A35988DFF3E78251E71FA0E272273975CEB0F5E2040CBD971198DF2D</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:57:23+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164869"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971569</ID>
      <Path>docs/002D57B1.pdf</Path>
      <Hash s:function="SHA-256">722289B51FB0D66066CB93C47FFA73578F97272DF0205974B3F20C756B27CE30</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:57:24+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164875"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971571</ID>
      <Path>docs/002D57B3.pdf</Path>
      <Hash s:function="SHA-256">FB8D7657402CBAA4644C48BD31D96F0D42BC3AFCFA3E1F03FEBE58627D64E055</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:08+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164882"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971573</ID>
      <Path>docs/002D57B5.pdf</Path>
      <Hash s:function="SHA-256">D265E85C19B8BCFD3F4A55D05EC84959C05D886E1F55A9714BD6479C4465160A</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:09+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164886"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971575</ID>
      <Path>docs/002D57B7.pdf</Path>
      <Hash s:function="SHA-256">F3692962D9BBDA21FC4EE74C1235A0D43DC28576F20B34D96D5B0CB3561CD985</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:10+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164884"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971577</ID>
      <Path>docs/002D57B9.pdf</Path>
      <Hash s:function="SHA-256">7B818CF3587FC11E39AEA91DC0E47CE13A7272CC8B62C311C4C779171B6C86BB</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:12+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164881"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971579</ID>
      <Path>docs/002D57BB.pdf</Path>
      <Hash s:function="SHA-256">26E455FFC70B8C68F96CE1D0A01320D9E79659DB6104CFD3746FFA9905AA0848</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:13+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164883"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971581</ID>
      <Path>docs/002D57BD.pdf</Path>
      <Hash s:function="SHA-256">68CD2CB7EAB47A4D355C313E247B1AA767C1A770065735AC48DB671E4CCF3101</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:15+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164880"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971583</ID>
      <Path>docs/002D57BF.pdf</Path>
      <Hash s:function="SHA-256">A2272100933748D013482DC6FF21E085F18AE21A173E27FD8CBEB5F458A9F418</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:16+02:00"/>
            <m:meta name="Data" value="2019-05-13"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200010"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164889"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971585</ID>
      <Path>docs/002D57C1.pdf</Path>
      <Hash s:function="SHA-256">E11257E423CEFFCE97720B700F078D6AA5A91420C2813549F0D6145CA5A24A38</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:17+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164887"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971587</ID>
      <Path>docs/002D57C3.pdf</Path>
      <Hash s:function="SHA-256">BA9DA8EFC6F57FCC5212B1B553B8E34E250F15EDB684941A32708DB200B2868A</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:19+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164888"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971589</ID>
      <Path>docs/002D57C5.pdf</Path>
      <Hash s:function="SHA-256">8622BEB8816D53CCEC44F32F381FC072760D6BC01995CBB6D9BCF748050B8BB1</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:58:21+02:00"/>
            <m:meta name="Data" value="2019-05-10"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200009"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164885"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971591</ID>
      <Path>docs/002D57C7.pdf</Path>
      <Hash s:function="SHA-256">A9076E11BD03952FC2D610438E789D963E9461B6331A9DDB6649979A97487BFE</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:59:10+02:00"/>
            <m:meta name="Data" value="2019-05-13"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200010"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164891"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
    <File s:format="application/pdf">
      <ID s:scheme="local">2971593</ID>
      <Path>docs/002D57C9.pdf</Path>
      <Hash s:function="SHA-256">70FECF3F3186552114D14AF57829BE76AA6F0FFC3473F620F2CA0B9B00E74712</Hash>
      <MoreInfo s:XMLScheme="http://andxor.it/tDoc/metadata.xsd">
        <EmbeddedMetadata>
          <m:metadata>
            <m:meta class="internal" name="Data inserimento" value="2019-05-13T20:59:11+02:00"/>
            <m:meta name="Data" value="2019-05-13"/>
            <m:meta name="Ragione Sociale" value="SCOTT ITALIA S.R.L."/>
            <m:meta name="Codice Cliente" value="200010"/>
            <m:meta name="Partita Iva" value="IT03770200164"/>
            <m:meta name="Numero Protocollo" value="9102164890"/>
          </m:metadata>
        </EmbeddedMetadata>
      </MoreInfo>
    </File>
  </FileGroup>
  <Process>
    <Agent s:type="organization" s:role="PreservationManager">
      <AgentName>
        <FormalName>KPNQwest Italia SpA</FormalName>
      </AgentName>
      <Agent_ID s:scheme="TaxCode">07957860153</Agent_ID>
    </Agent>
    <Agent s:type="person" s:role="PreservationManager">
      <AgentName>
        <NameAndSurname>
          <FirstName>Giuseppe</FirstName>
          <LastName>Baccanelli</LastName>
        </NameAndSurname>
      </AgentName>
      <Agent_ID s:scheme="TaxCode">BCCGPP64C01A940D</Agent_ID>
    </Agent>
    <Agent s:type="person" s:role="PreservationManager">
      <AgentName>
        <NameAndSurname>
          <FirstName>Giuseppe</FirstName>
          <LastName>Baccanelli</LastName>
        </NameAndSurname>
      </AgentName>
      <Agent_ID s:scheme="TaxCode">BCCGPP64C01A940D</Agent_ID>
    </Agent>
    <Agent s:type="organization" s:role="OtherRole" s:otherRole="Other role">
      <AgentName>
        <FormalName>Amministratore</FormalName>
      </AgentName>
    </Agent>
    <TimeReference>
      <TimeInfo>2019-05-19T02:00:25+02:00</TimeInfo>
    </TimeReference>
    <LawAndRegulations s:language="it">DPCM 3 dicembre 2013</LawAndRegulations>
  </Process>
</IdC>


A: There is no need to use OPENXML() and its companions sp_xml_preparedocument and `sp_xml_removedocument. They are kept just for backward compatibility with the obsolete SQL Server 2000.
Starting from SQL Server 2005 onwards, it supports XML data type methods and XQuery language. The example below is using one-to-many relationship simulation via CROSS APPLY clause and different aliases for different levels in the XML structure. The metadata fragment is shredded based on the @name attribute value.
Check it out.

SQL

-- directly from the XML file as a virtual DB table on the file system
;WITH XMLNAMESPACES (DEFAULT 'http://www.uni.com/U3011/sincro/', 'http://andxor.it/tDoc/metadata.xsd' AS m)
, rs (xmldata) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn 
   FROM OPENROWSET(BULK 'e:\Temp\Lotto_3.xml', SINGLE_BLOB) AS x
)
SELECT c.value('(Path/text())[1]', 'VARCHAR(30)') AS [Path]
   , x.value('(m:meta[@name="Data"]/@value)[1]','DATE') AS [Data1]
   , x.value('(m:meta[@name="Ragione Sociale"]/@value)[1]','VARCHAR(50)') AS [Name]
   , x.value('(m:meta[@name="Numero Protocollo"]/@value)[1]','VARCHAR(50)') AS [VatCode]
   , x.value('(m:meta[@name="Partita Iva"]/@value)[1]','VARCHAR(50)') AS [number]
   , tbl.xmldata.value('(/IdC/SelfDescription/ID/text())[1]','VARCHAR(50)') AS [ID]
FROM rs AS tbl
   CROSS APPLY tbl.xmldata.nodes('/IdC/FileGroup/File') AS t(c)
   CROSS APPLY t.c.nodes('MoreInfo/EmbeddedMetadata/m:metadata') AS meta(x);


Output

+-------------------+------------+---------------------+------------+---------------+---------------+
|       Path        |   Data1    |        Name         |  VatCode   |    number     |      ID       |
+-------------------+------------+---------------------+------------+---------------+---------------+
| docs/002D4DC0.pdf | 2019-04-30 | SCOTT ITALIA S.R.L. | 9102154398 | IT03770200164 | 1558224025077 |
| docs/002D4DC2.pdf | 2019-04-30 | SCOTT ITALIA S.R.L. | 9102154402 | IT03770200164 | 1558224025077 |
| docs/002D4DC4.pdf | 2019-04-30 | SCOTT ITALIA S.R.L. | 9102154401 | IT03770200164 | 1558224025077 |
| docs/002D4DC6.pdf | 2019-04-30 | SCOTT ITALIA S.R.L. | 9102154403 | IT03770200164 | 1558224025077 |
| docs/002D57A7.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164873 | IT03770200164 | 1558224025077 |
| docs/002D57A9.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164874 | IT03770200164 | 1558224025077 |
| docs/002D57AB.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164878 | IT03770200164 | 1558224025077 |
| docs/002D57AD.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164871 | IT03770200164 | 1558224025077 |
| docs/002D57AF.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164869 | IT03770200164 | 1558224025077 |
| docs/002D57B1.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164875 | IT03770200164 | 1558224025077 |
| docs/002D57B3.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164882 | IT03770200164 | 1558224025077 |
| docs/002D57B5.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164886 | IT03770200164 | 1558224025077 |
| docs/002D57B7.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164884 | IT03770200164 | 1558224025077 |
| docs/002D57B9.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164881 | IT03770200164 | 1558224025077 |
| docs/002D57BB.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164883 | IT03770200164 | 1558224025077 |
| docs/002D57BD.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164880 | IT03770200164 | 1558224025077 |
| docs/002D57BF.pdf | 2019-05-13 | SCOTT ITALIA S.R.L. | 9102164889 | IT03770200164 | 1558224025077 |
| docs/002D57C1.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164887 | IT03770200164 | 1558224025077 |
| docs/002D57C3.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164888 | IT03770200164 | 1558224025077 |
| docs/002D57C5.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164885 | IT03770200164 | 1558224025077 |
| docs/002D57C7.pdf | 2019-05-13 | SCOTT ITALIA S.R.L. | 9102164891 | IT03770200164 | 1558224025077 |
| docs/002D57C9.pdf | 2019-05-13 | SCOTT ITALIA S.R.L. | 9102164890 | IT03770200164 | 1558224025077 |
+-------------------+------------+---------------------+------------+---------------+---------------+

