提取XML数据 [英] Extracting XML Data
问题描述
您好我有一张桌子，其中包含以下几个字段
Hi I have a table and that contains several field as follow
Seq_id（int） COMPANY_NAME （VARCHAR）           Opportunityid            &NBSP ; 消息
（XML ）
Seq_id(int) company_name (Varchar) Opportunityid Message (XML )
1                            ABC                                       1001                            
AS Under
1 ABC 1001 AS Under
2                            BCD                                     
1002
2 BCD 1002
这是我在消息字段中的XML数据
< Cust_Agreement_Set>
< Upload_Reason = QUOT; EvaluateAndExport" Extraction_Time =" 20020617 15:05:55">
< Customer_Record Pricing_Area_ID =" PQR" Pricing_Area_Desc = QUOT;&度Acc QUOT; CR_ID = QUOT; 000660" CR_DESC ="ABC Corp .-" INDUSTRY_ID = QUOT; Z03" INDUSTRY_DESC ="PD Transp& amp; amp; Trvl Svc" CLIENT_CLASS_ID = QUOT; Z1" CLIENT_CLASS_DESC = QUOT;基金会" SUBCSG_ID = QUOT; ZSUBCG0076"
SUBCSG_DESC =" Prod - Transportation& amp; amp;旅行服务" DIAMOND_STATUS_ID = QUOT; ED" DIAMOND_STATUS_DESC ="EmergingDiamond Cust">
< Header_Record Cust_Agreement_ID =" 3004" Master_Agreement_ID = QUOT; 16" Contract_Type = QUOT;定期" START_DATE = QUOT; 20090410" END_DATE = QUOT; 20090430" Quote_Start_Date = QUOT; 20090414" Quote_End_Date = QUOT; 20090410" CURRENCY_CODE = QUOT; USD" UOM_Code = QUOT; HOUR" Amended_Flag =" N">
<调整名称="D-人工成本应急"值= QUOT; 334.096" PERCENT_VALUE = QUOT; 0.2" />
$
< Adjustment Name =" D-Billable Expense％"值= QUOT; 0.0" PERCENT_VALUE = QUOT; 0.0" />
$
< Adjustment Name =" D-Billable Exp Contingency"值= QUOT; 0.0" PERCENT_VALUE = QUOT; 0.2" />
$
< Adjustment Name =" VAT"值= QUOT; 0.0" PERCENT_VALUE = QUOT; 0.0" />
$
< Adjustment Name =" D-NB Expense％"值= QUOT; 0.0" PERCENT_VALUE = QUOT; 0.0" />
$
<调整名称="D-NB Exp Contingency"值= QUOT; 0.0" PERCENT_VALUE = QUOT; 0.2" />
$
< Adjustment Name =" Pyramid Margin - Distributed"值= QUOT; 277.27694" PERCENT_VALUE = QUOT;" />
$
< Adjustment Name =" Billing"值= QUOT; 474.40318657376" PERCENT_VALUE = QUOT;" />
$
< Metric Name =" Target CCI％"值=" 0.5 />
<度量名称="金字塔方差（CCI％）"值= QUOT; 0.000123" />
$
<度量名称="其他方差（CCI％）"值= QUOT; -0.02" />
$
< Metric Name =" Actual CCI％"值= QUOT; 0.4876757416353402" />
$
< Metric Name =" Target Price excl Expenses"值= QUOT; 4697.09387755103" />
$
< Metric Name =" Price excl Expenses"值= QUOT; 474.40318657376" />
$
< Metric Name =" Billable Expenses"值= QUOT; 0.0" />
$
<度量标准名称="总交付成本"值= QUOT; 241803.933" />
$
< Metric Name =" Contingency％"值= QUOT; 0.2" />
$
< Metric Name =" Subcontractors Cost Mix％"值= QUOT; 0.0" />
$
<指标名称="每单位人工价格不包括"值= QUOT; 218.5066681419323" />
$
< Metric Name =" Total Net Revenue"值= QUOT; 471974.40318657376" />
$
< Dimension Name =" External_Deal"值=" 0000169621">
< Attribute Name =" MC_PCT"值= QUOT; 20" />
$
< Attribute Name =" TC_PCT"值= QUOT; 30英寸; />
$
< Attribute Name =" SI_PCT"值= QUOT; 50" />
$
< Attribute Name =" WIN_PROBABILITY"值= QUOT; 80 QUOT; />
$
< Attribute Name =" OPP_QA_DIRECTOR_DESC"值="未分配" />
<Cust_Agreement_Set>
<Upload_Reason="EvaluateAndExport" Extraction_Time="20020617 15:05:55">
<Customer_Record Pricing_Area_ID="PQR" Pricing_Area_Desc="Acc" CR_ID="000660" CR_DESC="ABC Corp.-" INDUSTRY_ID="Z03" INDUSTRY_DESC="PD Transp & Trvl Svc" CLIENT_CLASS_ID="Z1" CLIENT_CLASS_DESC="Foundation" SUBCSG_ID="ZSUBCG0076"
SUBCSG_DESC="Prod - Transportation & Travel Services" DIAMOND_STATUS_ID="ED" DIAMOND_STATUS_DESC="EmergingDiamond Cust">
<Header_Record Cust_Agreement_ID="3004" Master_Agreement_ID="16" Contract_Type="Regular" Start_Date="20090410" End_Date="20090430" Quote_Start_Date="20090414" Quote_End_Date="20090410" Currency_Code="USD" UOM_Code="HOUR" Amended_Flag="N">
<Adjustment Name="D-Labor Cost Contingency" Value="334.096" Percent_Value="0.2" />
<Adjustment Name="D-Billable Expense %" Value="0.0" Percent_Value="0.0" />
<Adjustment Name="D-Billable Exp Contingency" Value="0.0" Percent_Value="0.2" />
<Adjustment Name="VAT" Value="0.0" Percent_Value="0.0" />
<Adjustment Name="D-NB Expense %" Value="0.0" Percent_Value="0.0" />
<Adjustment Name="D-NB Exp Contingency" Value="0.0" Percent_Value="0.2" />
<Adjustment Name="Pyramid Margin - Distributed" Value="277.27694" Percent_Value="" />
<Adjustment Name="Billing" Value="474.40318657376" Percent_Value="" />
<Metric Name="Target CCI%" Value="0.5 />
<Metric Name="Pyramid Variance (CCI%)" Value="0.000123" />
<Metric Name="Other Variance (CCI%)" Value="-0.02" />
<Metric Name="Actual CCI%" Value="0.4876757416353402" />
<Metric Name="Target Price excl Expenses" Value="4697.09387755103" />
<Metric Name="Price excl Expenses" Value="474.40318657376" />
<Metric Name="Billable Expenses" Value="0.0" />
<Metric Name="Total Delivery Costs" Value="241803.933" />
<Metric Name="Contingency%" Value="0.2" />
<Metric Name="Subcontractors Cost Mix%" Value="0.0" />
<Metric Name="Labor Price per Unit excl Cont" Value="218.5066681419323" />
<Metric Name="Total Net Revenue" Value="471974.40318657376" />
<Dimension Name="External_Deal" Value="0000169621">
<Attribute Name="MC_PCT" Value="20" />
<Attribute Name="TC_PCT" Value="30" />
<Attribute Name="SI_PCT" Value="50" />
<Attribute Name="WIN_PROBABILITY" Value="80" />
<Attribute Name="OPP_QA_DIRECTOR_DESC" Value="Not Assigned" />
我想将此XML数据仅移动到我的目标表中控制我的目标表，但我如何提取这些数据，并且有超过10,000行
推荐答案
一般来说，XML数据可以视为字符串。请参阅 http://www.developer.com/net/net/article.php/11087_3406251_3/Work-with-XML-Data-Type-in-SQL-Server-2005-from-ADONET-20.htm 用于
a样本用法。
Generally, XML data can be treated as string. See http://www.developer.com/net/net/article.php/11087_3406251_3/Work-with-XML-Data-Type-in-SQL-Server-2005-from-ADONET-20.htm for a sample usage.
这篇关于提取XML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
