从sql中的xml获取属性和元素值

巴里

我有以下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

我对此还比较陌生,似乎无法弄清楚这一点,请帮忙,因为这可能很简单!

模糊

这是您需要的:

SELECT 
    AttributeName = ChildControls.value('../@parent','varchar(50)'),
    NodeName = ChildControls.value('local-name(.)', 'varchar(50)'),
    NodeValue = ChildControls.value('(.)[1]', 'varchar(50)') 
FROM @GetQuoteXML.nodes('//ChildControls/*') AS ChildControlTable(ChildControls)

结果:

在此处输入图片说明

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章