破碎xml的问题 [英] Problem in shredding xml

问题描述

您好，

我有xml格式的数据。我需要在表格中插入这些数据。

这是我正在处理的xml。

Hello ,
I have data in xml format. I need to insert this data in a table.
This is the xml that i am working on.

<pensioner-master xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="">
  <Pensioner-details>
     <location>
       <pensioner-state>65</pensioner-state>
      <pensioner-district>6501</pensioner-district>
     <location>

     <details>
       <gender>F</gender>
       <age>78</age>
     <details>
  </Pensioner-details>
</pensioner-master>

这是迄今为止我所做的

This is so far what i have done

Select [pensioner-state] =Pen.value('(pensioner-state)[1]','int'),
        [pensioner-district]=Pen.value('(pensioner-district)[1]','int')
        from @Pensioners.nodes('/pensioner-master/Pensioner-details/location') as Tbl(Pen)

这段代码给了我位置节点下的州和区。但我还需要使用相同的select语句在详细信息节点下的性别和年龄。任何建议我应该怎么做？

this code gives me state and district under the location node . but i also need gender and age which are under details node , using same select statement. Any suggestion how should i do this ??

推荐答案

< pensioner-master xmlns：xsi =http://www.w3.org/2001/XMLSchema-实例xmlns =>

< pensioner-details>

< location>

< pensioner-state> 65< /养老金领取者州>

< pensioner-district> 6501< / pensioner-district>

< / location>



< details>

< gender> F< / sex>

< age> 78< / age>

< / details>

< / pensioner-details>

< / pensioner-master>



选择[养老金领取者] = Pen.value（'（location / pensioner-state）[1]'，'int'），

[养老金领域] = Pen.value（' （location / pensioner-district）[1]'，'int'），

[性别] = Pen.value（'（details / gender）[1]'，'varchar（5）'来自@ Pensioners.nodes的
（'/ pensioner-master / Pensioner-details'）为Tbl（笔）

<pensioner-master xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="">
<pensioner-details>
<location>
<pensioner-state>65</pensioner-state>
<pensioner-district>6501</pensioner-district>
</location>

<details>
<gender>F</gender>
<age>78</age>
</details>
</pensioner-details>
</pensioner-master>

Select [pensioner-state] = Pen.value('(location/pensioner-state)[1]','int'),
[pensioner-district]=Pen.value('(location/pensioner-district)[1]','int'),
[gender]=Pen.value('(details/gender)[1]','varchar(5)')
from @Pensioners.nodes('/pensioner-master/Pensioner-details') as Tbl(Pen)

这篇关于破碎xml的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！