sql-server - 使用 SQL Server 中的查询从 XML 推断数据
问题描述
我正在尝试从我在页面底部报告的 XML 文档中获取一系列值,使用 DB SQL 服务器内部的 xpath 查询。我绝对不是这方面的专家,我为不准确的地方道歉。我按照该站点上的说明进行管理,并在网上找到执行以下程序(在我的电脑上,XML 文件位于路径 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;
这让我可以从有问题的 XML 中读取一些标签。我感兴趣的值是第一个 TAG 中包含的值
<ID s: scheme = "local"> 1558224025077 </ID>
在所有标签值中
<Path> docs / 002D4DC0.pdf </ Path>
以及各个标签的标签,属于每个标签文档的同一节点
<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"
我想获得几列,每列包含每组的数据,如下所示:
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 ....
并且在 ID 列中可能始终具有相同的值 = 1558224025077
但是,通过我设置的程序,我只得到,寻找标记“路径”和“ID s:方案”的值,重复(对于 1114 行,对应于文件中存在的“分组”的数量) 的第一个标签“路径”中存在的相同值相反,我想像前面的示例一样显示所有值。
是否可以在 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
相反,允许我在 XPath Tester 应用程序中正确提取“PAth”标记的所有值的表达式是
//*[name()= 'Path']
同时与
//meta[1]/@value
我根据索引 1,2 等..“值”标签的值
但是在 sql server 中我不能应用它们。
我非常感谢谁可以帮助我。
再见
这是我的 XML 示例(其他具有相同结构的可能更长)
<?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>
解决方案
没有必要使用OPENXML()
及其同伴sp_xml_preparedocument
和`sp_xml_removedocument。保留它们只是为了与过时的 SQL Server 2000 向后兼容。
从 SQL Server 2005 开始,它支持 XML 数据类型方法和 XQuery 语言。下面的示例使用一对多关系模拟 viaCROSS APPLY
子句和 XML 结构中不同级别的不同别名。元数据片段基于@name
属性值被粉碎。
看看这个。
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);
输出
+-------------------+------------+---------------------+------------+---------------+---------------+
| 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 |
+-------------------+------------+---------------------+------------+---------------+---------------+
推荐阅读
- r - 在R中合并两个具有不同行的数据框
- fork - 我想在这行代码中使用 posix_spawn insted fork 或 execve 请帮助我
- amazon-web-services - CloudFormation 错误地报告 CloudFront 标签中的偏差
- javascript - 如何在不触发另一个函数的情况下将值从一个函数传递到另一个函数?
- lua - 避免 RawBlocks 之间的换行
- javascript - 如何获取当前浏览器支持的所有 JS API 列表?
- character-encoding - 字符串作为集合的可逆编码
- c# - 从通用单例基类继承的子类的构造函数必须需要公共权限。如何解决?
- python - 替换列表中的一系列重复出现?
- html - 引导程序(mdb)折叠不显示在第一次单击中