如何使用SQL Server遍历每个潜在的XML子元素

Question

我有一个包含超过45K个联系人的大型XML文件，我需要将它们的子元素事务迭代到一个SQL表中。我已经看过几个解决方案，使用value()， node()等…，但是没有一个示例的XML结构与我的接近:

<Contacts>
<Contact>
<ContactID>1234</ContactID>
<ContactName>’John Doe’</ContactName>
<DOB>09031978</DOB>
<Address>’123 Main Street’</Address>
<Transactions>
<Transaction>
<TransactionID>4490</TransactionID>
<ProductName>’Recliner’</ProductName>
<Cost>123.00</Cost>
<PurchaseDate>07042020
</Transaction>
<Transaction>
<TransactionID>5678</TransactionID>
<ProductName>’Lamp’</ProductName>
<Cost>45.00</Cost>
<PurchaseDate>07042020
<Transaction>
</Transactions>
</Contact>
<Contact>
<ContactID>4567</ContactID>
<ContactName>’Jane Doe’</ContactName>
<DOB>05191984</DOB>
<Address>’567 Fake Street’</Address>
<Transactions>
<Transaction>
<TransactionID>4378</TransactionID>
<ProductName>’Coffee Table’</ProductName>
<Cost>225.00</Cost>
<PurchaseDate>07042018
</Transaction>
</Transactions>
</Contact>
</Contacts>

我需要这些数据的结果如下:

PurchaseDate

尽量避免sp_xml_preparedocument，因为它使用了大量的内存，SQL Server不能使用它，直到你记得通过调用sp_xml_removedocument来释放它。

你所要求的可以很容易地实现使用节点()与交叉应用，例如(修复您的XML样本后):

declare @doc xml = N'<Contacts>
<Contact>
<ContactID>1234</ContactID>
<ContactName>’John Doe’</ContactName>
<DOB>09031978</DOB>
<Address>’123 Main Street’</Address>
<Transactions>
<Transaction>
<TransactionID>4490</TransactionID>
<ProductName>’Recliner’</ProductName>
<Cost>123.00</Cost>
<PurchaseDate>07042020</PurchaseDate>
</Transaction>
<Transaction>
<TransactionID>5678</TransactionID>
<ProductName>’Lamp’</ProductName>
<Cost>45.00</Cost>
<PurchaseDate>07042020</PurchaseDate>
</Transaction>
</Transactions>
</Contact>
<Contact>
<ContactID>4567</ContactID>
<ContactName>’Jane Doe’</ContactName>
<DOB>05191984</DOB>
<Address>’567 Fake Street’</Address>
<Transactions>
<Transaction>
<TransactionID>4378</TransactionID>
<ProductName>’Coffee Table’</ProductName>
<Cost>225.00</Cost>
<PurchaseDate>07042018</PurchaseDate>
</Transaction>
</Transactions>
</Contact>
</Contacts>';
select
Cont.value('ContactID[1]', 'int') as ContactID
,Trans.value('TransactionID[1]', 'int') as TransactionID
,Trans.value('ProductName[1]', 'nvarchar(50)') as ProductName
,Trans.value('Cost[1]', 'float') as Cost
,convert(date, concat(substring(purDate,1,2), '/', substring(purDate,3,2), '/', substring(purDate,5,4)), 101) as PurchaseDate
from @doc.nodes('//Contact') nodes1(Cont)
cross apply nodes1.Cont.nodes('Transactions/Transaction') nodes2(Trans)
outer apply (
select purDate = Trans.value('PurchaseDate[1]', 'nvarchar(8)')
) temp;

收益率:

TransactionIDProductNamePurchaseDate4490躺椅上的1232020-07-045678"灯">2252018-07-04