我有以下XML,需要将此数据提取到sql表中以获取属性名称和所有元素值
declare @GetQuoteXML xml
set @GetQuoteXML = '<QuoteRequest>
<QuoteRisk>
<ChildControls parent = "MainPerson">
<OccupationID>347</OccupationID>
<OccupationDescription />
<OccupationOtherDescription>accountant</OccupationOtherDescription>
</ChildControls>
<ChildControls parent = "OtherPerson">
<OccupationID>200</OccupationID>
<OccupationDescription />
<OccupationOtherDescription>engineer</OccupationOtherDescription>
</ChildControls>
</QuoteRisk>
</QuoteRequest>'
我的SQL是
SELECT
AttributeName = ChildControls.value('(//ChildControls/@parent)[1]','varchar(50)'),
NodeName = ChildControls.value('local-name(.)', 'varchar(50)'),
NodeValue = ChildControls.value('(.)[1]', 'varchar(50)')
FROM @GetQuoteXML.nodes('//ChildControls/*') AS ChildControlTable(ChildControls)
但结果似乎总是在“ Mainperson”属性下,并且在AttributeName列中不返回“ OtherPerson”
AttributeName NodeName NodeValue
MainPerson OccupationID 347
MainPerson OccupationDescription
MainPerson OccupationOtherDescription accountant
MainPerson OccupationID 200
MainPerson OccupationDescription
MainPerson OccupationOtherDescription engineer
我希望结果看起来像是:
AttributeName NodeName NodeValue
MainPerson OccupationID 347
MainPerson OccupationDescription
MainPerson OccupationOtherDescription accountant
OtherPerson OccupationID 200
OtherPerson OccupationDescription
OtherPerson OccupationOtherDescription engineer
我对此还比较陌生,似乎无法弄清楚这一点,请帮忙,因为这可能很简单!
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句