时间戳

首页 > 解决方案 > 仅将特定 XML 参数导入 SQL Server

sql-server - 仅将特定 XML 参数导入 SQL Server

问题描述

我一直在阅读和阅读如何将 xml 导入我的 SQL Server，我非常接近。

我遇到的最后一个问题是 xml 文件中有数百个项目。有些列出了一些附加参数，因此导入错误并且不会加载。

我只需要这些参数中的 5 个来拉入，但不知道如何拉出这些参数。

我的代码在这里；如果我只是将一两个设备放在testing.xml只有这些参数的文件中，它就可以工作。如果其中一个设备中有新参数，则它不起作用。

我真的只需要deviceId,deviceDescription并且versionModelNumber在所有记录中。

INSERT INTO DeviceDB (publicDeviceRecordKey, publicVersionStatus, deviceRecordStatus, publicVersionNumber, publicVersionDate, deviceCommDistributionEndDate, deviceCommDistributionStatus, deviceId, deviceIdType, deviceIdIssuingAgency, containsDINumber, pkgQuantity, pkgDiscontinueDate, pkgStatus, pkgType, brandName, versionModelNumber, catalogNumber, dunsNumber, companyName, deviceCount, deviceDescription, DMExempt, premarketExempt, deviceHCTP, deviceKit, deviceCombinationProduct, singleUse, lotBatch, serialNumber, manufacturingDate, expirationDate, donationIdNumber, labeledContainsNRL, labeledNoNRL, MRISafetyStatus, rx, otc, phone, phoneExtension, email, gmdnPTName, gmdnPTDefinition, productCode, productCodeName, deviceSterile, sterilizationPriorToUse)
SELECT
    MY_XML.Customer.query('publicDeviceRecordKey').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('publicVersionStatus').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('deviceRecordStatus').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('publicVersionDate').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('publicPublishDate').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('publicCommDistributionEndDate').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('publicCommDistributionStatus').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('identifiers/identifier/deviceId').value('.', 'VARCHAR(150)'),
    MY_XML.Customer.query('identifiers/identifier/deviceIdType').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('identifiers/identifier/deviceIdIssuingAgency').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('identifiers/identifier/containsDINumber').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('identifiers/identifier/pkgQuantity').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('identifiers/identifier/pkgDiscontinueDate').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('identifiers/identifier/pkgStatus').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('identifiers/identifier/pkgType').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('brandName').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('versionModelNumber').value('.', 'VARCHAR(300)'),
    MY_XML.Customer.query('catalogNumber').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('dunsNumber').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('companyName').value('.', 'VARCHAR(300)'),
    MY_XML.Customer.query('deviceCount').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('deviceDescription').value('.', 'VARCHAR(300)'),
    MY_XML.Customer.query('DMExempt').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('premarketExempt').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('deviceHCTP').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('deviceKit').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('deviceCombinationProduct').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('singleUse').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('lotBatch').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('serialNumber').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('manufacturingDate').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('expirationDate').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('donationIdNumber').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('labeledContainsNRL').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('labeledNoNRL').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('MRISafteyStatus').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('rx').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('otc').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('contacts/customerContact/phone').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('contacts/customerContact/phoneExtension').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('contacts/customerContact/email').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('gmdnTerms/gmdn/gmdnPTName').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('gmdnTerms/gmdn/gmdnPTDefinition').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('productCodes/fdaProductCode/productCode').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('productCodes/fdaProductCode/productCodeName').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('sterilization/deviceSterile').value('.', 'VARCHAR(50)'),
    MY_XML.Customer.query('sterilization/sterilizationPriorToUse').value('.', 'VARCHAR(50)')
FROM
    (SELECT CAST(MY_XML AS xml)
     FROM OPENROWSET(BULK 'C:\testing.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
CROSS APPLY 
    MY_XML.nodes('device') AS MY_XML (Customer);`

标签： sql-serverxml

解决方案

尝试将 XQuery 移入 value() 函数并删除 query() 函数。XQuery 将需要更新以确保通过“[1]”仅返回一个值，即使从不超过 1 个值。如果该值不存在，则 value() 将返回 null。实际上，对 null query() 结果调用 value() 可能是问题所在。

顺便说一句，如果可以有超过 1 个值，您必须决定是否需要数据。如果是这样，那将是不同的。

https://docs.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver15

我不经常使用 XML，所以我需要大约 100 次尝试才能获得正确的语法和路径。这是您选择的第一行的我的 WAG。

MY_XML.Customer.value('(./publicDeviceRecordKey)[1]', 'VARCHAR(50)') as [publicDeviceRecordKey]

推荐阅读