SQL Server - обновление данных XML из другой таблицы
У меня есть XML-файл, который я загрузил в SQL Server и успешно разбил на столбцы.
Это основано на моих вопросах: путаница с пространством имен XML в SQL
Вот образец XML-файла: я удалил повторяющиеся продукты, чтобы сократить файл:
<batch_request xmlns="http://sancrt.mpi.govt.nz/ecert/2013/batch-submission-schema.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://sancrt.mpi.govt.nz/ecert/2013/batch-submission-schema.xsd batchsubmission- schema.xsd">
<raise_eligibility_document>
<ed:Certificate xmlns="http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd" xmlns:ed="http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd">
<ed:Identifiers>
<ed:CertificateID>99999</ed:CertificateID>
<ed:TemplateID>ED1</ed:TemplateID>
</ed:Identifiers>
<ed:DepartureDate>2021-05-14</ed:DepartureDate>
<ed:Parties>
<ed:ConsignorID>MEABC</ed:ConsignorID>
<ed:ConsigneeID>FLIGHT1</ed:ConsigneeID>
</ed:Parties>
<ed:Transport>
<ed:Ports>
<ed:LoadingPortID>XYZZ</ed:LoadingPortID>
</ed:Ports>
<ed:FinalDestination>KHALIFA, United Arab Emirates</ed:FinalDestination>
<ed:TransportMode>1</ed:TransportMode>
<ed:LocalCarrier>MDH2</ed:LocalCarrier>
<ed:CarrierName>TIANJIN BRIDGE</ed:CarrierName>
<ed:ConveyanceReference>V152</ed:ConveyanceReference>
</ed:Transport>
<ed:Remarks>
<ed:Remark>
<ed:RemarkType>UnofficialInformation</ed:RemarkType>
<ed:RemarkValue>Vessel ETD - 24/05/21\nTARE WEIGHT - 3010 KGS</ed:RemarkValue>
</ed:Remark>
</ed:Remarks>
<ed:Products>
<ed:Product>
<ed:ProductItem>1</ed:ProductItem>
<ed:Origin>AO</ed:Origin>
<ed:Description>This is Product1 of many</ed:Description>
<ed:CommonName>Bovine</ed:CommonName>
<ed:EligibilityCountries>
<ed:EligibilityCountryID>AE</ed:EligibilityCountryID>
</ed:EligibilityCountries>
<ed:FirstExpiryDate>2021-06-28</ed:FirstExpiryDate>
<ed:LastExpiryDate>2021-06-28</ed:LastExpiryDate>
<ed:IntendedUse>consumption</ed:IntendedUse>
<ed:GrossWeight unitCode="KGM">130.2</ed:GrossWeight>
<ed:NetWeight unitCode="KGM">120.4</ed:NetWeight>
<ed:Remarks>
<ed:Remark>
<ed:RemarkType>Product Statement</ed:RemarkType>
<ed:RemarkValue>Item No. 81274</ed:RemarkValue>
</ed:Remark>
</ed:Remarks>
<ed:Classifications>
<ed:Classification>
<ed:ClassificationType>Temperature</ed:ClassificationType>
<ed:ClassificationValue>chilled</ed:ClassificationValue>
</ed:Classification>
<ed:Classification>
<ed:ClassificationType>Halal Product</ed:ClassificationType>
<ed:ClassificationValue>1</ed:ClassificationValue>
</ed:Classification>
<ed:Classification>
<ed:ClassificationType>New Zealand Harmonised System Code</ed:ClassificationType>
<ed:ClassificationValue>020120</ed:ClassificationValue>
</ed:Classification>
</ed:Classifications>
<ed:Containers>
<ed:Container>
<ed:ID>CGM123456</ed:ID>
<ed:Seals>
<ed:ID>Y123345546566</ed:ID>
</ed:Seals>
</ed:Container>
</ed:Containers>
<ed:Packaging>
<ed:Package>
<ed:Quantity>9</ed:Quantity>
<ed:Type>CT</ed:Type>
<ed:Level>1</ed:Level>
<ed:ShippingMarks>
<ed:Name>9866545</ed:Name>
</ed:ShippingMarks>
</ed:Package>
</ed:Packaging>
<ed:Processes>
<ed:Process>
<ed:ProcessTypeCode>SLT</ed:ProcessTypeCode>
<ed:StartDate>2021-05-03</ed:StartDate>
<ed:EndDate>2021-05-03</ed:EndDate>
<ed:DateOverride>false</ed:DateOverride>
<ed:Premise>
<ed:ID>ABC123</ed:ID>
</ed:Premise>
</ed:Process>
<ed:Process>
<ed:ProcessTypeCode>PRO</ed:ProcessTypeCode>
<ed:StartDate>2021-05-04</ed:StartDate>
<ed:EndDate>2021-05-04</ed:EndDate>
<ed:DateOverride>false</ed:DateOverride>
<ed:Premise>
<ed:ID>ABC123</ed:ID>
</ed:Premise>
</ed:Process>
</ed:Processes>
</ed:Product>
</ed:Certificate>
</raise_eligibility_document>
</batch_request>
Вот выбор, который я использовал для измельчения XML:
EXEC('INSERT INTO #XmlEDIImport(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''', SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
select @xml = (SELECT xml_data from #XmlEDIImport)
--select @xml
;with xmlnamespaces (
'http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd' as multiple,
'http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd' as submission
)
select
header.value(N'(submission:Identifiers/submission:CertificateID/text())[1]', N'nvarchar(20)') as ecert_number
, product.value(N'(submission:ProductItem/text())[1]', N'int') as ProductItem
, product.value(N'(submission:FirstExpiryDate/text())[1]', 'DATE') as first_expiry
, product.value(N'(submission:LastExpiryDate/text())[1]', 'DATE') as last_expiry
, product.value(N'(submission:Remarks/submission:Remark/submission:RemarkValue/text())[1]', N'nvarchar(20)') as xml_item_no
into #flite_e_coli_cert
from @xml.nodes('//submission:Certificate') as ci(header)
outer apply @xml.nodes('//submission:Certificate//submission:Products//submission:Product') as p(product)
order by ProductItem
После связывания приведенного выше с другой таблицей я получаю следующий результат с производными столбцами:
для простоты назовем эту таблицу "for_update"
Теперь мне нужно использовать производный столбец для обновления исходного XML (таблица #XmlEDIImport), который был загружен в SQL Server.
В частности, если элемент продукта совпадает с Node ProductItem в XML, мне нужно заменить как «FirstExpiryDate», так и «LastExpiryDate» значением из столбца first_expiry_new и Last_expiry_new из таблицы «for_update».
Вот попытка, но я не уверен, как ссылаться на данные таблицы в части WITH обновления. Я даже не уверен, что это правильно.
select @xml = (SELECT xml_data from #XmlEDIImport)
;with xmlnamespaces (
'http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd' as multiple,
'http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd' as submission
)
update
#XmlEDIImport
Set
xml_data.modify('replace value of (submission:FirstExpiryDate/text())[1] with "01/01/1990"' )
from
@xml.nodes('//submission:Certificate//submission:Products//submission:Product') as p(product)
inner join #for_update for_update
on product.value(N'(submission:ProductItem/text())[1]', N'int') = for_update.ProductItem
select xml_data as updated from #XmlEDIImport