我需要重写一个旧的DTS/SQL2000进程，该进程将以varchar形式存储的xml数据从表导出到xml文件，只是为了读取并填充一些表，如合同/客户/业务活动。我想直接查询xml列，但总是得到Blank或NULL，这取决于我使用的方法，我真的很难。我最新的基于另一个答案的快速版本是…
SELECT
XML_MESSAGE.value('(Transaction_Id/text())[1]','varchar(100)') AS TransactionID
FROM dbo.XML_REPOSITORY t
OUTER APPLY t.XML_MESSAGE.nodes('Contract_Interface') AS bm(XMLData)
--OUTER APPLY bm.XML_MESSAGE.nodes('food') AS f(XMLData)
然而，我已经为每个集合(合同/客户等(创建了一个基于旧xsd文件的Schema集合，所以真正的问题是我如何使用它来提取相关数据？xml的精简版本如下。。
<Contract_Interface xmlns="http://company.com/backoffice/types/common" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://comapny.com/backoffice/types/common http://dataplace/xmlschemas/Policy_Transaction_Interface_1_1_9.xsd">
<Transaction_Id>48244272268</Transaction_Id>
<Source_System_Cd>SystemName</Source_System_Cd>
<Business_Activity>
<Contract_Id>169929</Contract_Id>
<Business_Activity_Type_Cd>01</Business_Activity_Type_Cd>
<Business_Activity_Type_Desc>New Binder</Business_Activity_Type_Desc>
<Out_Of_Sequence_Ind>N</Out_Of_Sequence_Ind>
<Effective_Dt>4/18/2021 12:00:00 AM</Effective_Dt>
<Expiration_Dt>4/18/2022 12:00:00 AM</Expiration_Dt>
<Premium_Amt>123678.00</Premium_Amt>
<Contract>
<Customer>
<Customer_Id>45678</Customer_Id>
<ODS_Customer_Id>6789</ODS_Customer_Id>
<Insured_Info_Overriden_Cd>N</Insured_Info_Overriden_Cd>
<Insured_Info_Overriden_Desc>No</Insured_Info_Overriden_Desc>
<Insured_Nm>Make Money PLC</Insured_Nm>
<Insured_Address>
<Address_Line_1>Alex Jones Terrace</Address_Line_1>
<Address_Line_2 />
<City_Nm>Houston</City_Nm>
<State_Cd>TX</State_Cd>
<State_Desc>Texas</State_Desc>
<Postal_Cd>77002</Postal_Cd>
<Country_Cd>US</Country_Cd>
<Country_Desc>United States</Country_Desc>
</Insured_Address>
</Customer>
<Producer>
<Producer_Id>33333</Producer_Id>
<ODS_Producer_Id>1234</ODS_Producer_Id>
<Producer_No>1234</Producer_No>
<Producer_Nm>Brokerage Limited</Producer_Nm>
<Billing_Address_Overridden_Ind>false</Billing_Address_Overridden_Ind>
<Producer_Billing_Address>
<Address_Line_1>Suite 1000</Address_Line_1>
<Address_Line_2>Free Road</Address_Line_2>
<City_Nm>Hamilton</City_Nm>
<State_Cd />
<State_Desc />
<Postal_Cd>HM11</Postal_Cd>
<Country_Cd>BM</Country_Cd>
<Country_Desc>Bermuda</Country_Desc>
</Producer_Billing_Address>
</Producer>
</Contract>
</Business_Activity>
</Contract_Interface>
正如我所提到的，您需要定义您的命名空间。这使用了一个变量，而不是你的表，但给了你一个叠加的想法：
DECLARE @XML xml = '<Contract_Interface xmlns="http://company.com/backoffice/types/common" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://comapny.com/backoffice/types/common http://dataplace/xmlschemas/Policy_Transaction_Interface_1_1_9.xsd">
<Transaction_Id>48244272268</Transaction_Id>
<Source_System_Cd>SystemName</Source_System_Cd>
<Business_Activity>
<Contract_Id>169929</Contract_Id>
<Business_Activity_Type_Cd>01</Business_Activity_Type_Cd>
<Business_Activity_Type_Desc>New Binder</Business_Activity_Type_Desc>
<Out_Of_Sequence_Ind>N</Out_Of_Sequence_Ind>
<Effective_Dt>4/18/2021 12:00:00 AM</Effective_Dt>
<Expiration_Dt>4/18/2022 12:00:00 AM</Expiration_Dt>
<Premium_Amt>123678.00</Premium_Amt>
<Contract>
<Customer>
<Customer_Id>45678</Customer_Id>
<ODS_Customer_Id>6789</ODS_Customer_Id>
<Insured_Info_Overriden_Cd>N</Insured_Info_Overriden_Cd>
<Insured_Info_Overriden_Desc>No</Insured_Info_Overriden_Desc>
<Insured_Nm>Make Money PLC</Insured_Nm>
<Insured_Address>
<Address_Line_1>Alex Jones Terrace</Address_Line_1>
<Address_Line_2 />
<City_Nm>Houston</City_Nm>
<State_Cd>TX</State_Cd>
<State_Desc>Texas</State_Desc>
<Postal_Cd>77002</Postal_Cd>
<Country_Cd>US</Country_Cd>
<Country_Desc>United States</Country_Desc>
</Insured_Address>
</Customer>
<Producer>
<Producer_Id>33333</Producer_Id>
<ODS_Producer_Id>1234</ODS_Producer_Id>
<Producer_No>1234</Producer_No>
<Producer_Nm>Brokerage Limited</Producer_Nm>
<Billing_Address_Overridden_Ind>false</Billing_Address_Overridden_Ind>
<Producer_Billing_Address>
<Address_Line_1>Suite 1000</Address_Line_1>
<Address_Line_2>Free Road</Address_Line_2>
<City_Nm>Hamilton</City_Nm>
<State_Cd />
<State_Desc />
<Postal_Cd>HM11</Postal_Cd>
<Country_Cd>BM</Country_Cd>
<Country_Desc>Bermuda</Country_Desc>
</Producer_Billing_Address>
</Producer>
</Contract>
</Business_Activity>
</Contract_Interface>';
WITH XMLNAMESPACES(DEFAULT 'http://company.com/backoffice/types/common')
SELECT bm.XMLData.value('(Transaction_Id/text())[1]','varchar(100)') AS TransactionID --Should this not be a int or bigint?
FROM @XML.nodes('Contract_Interface') AS bm(XMLData);