Problem in shredding xml

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

SQL

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)

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 ??

Posted 11-Mar-15 20:48pm

Aakash Sharma

Updated 11-Mar-15 20:53pm

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abdulnazark · Answer 1 · 2015-03-12T06:01:00

<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)

Problem in shredding xml

1 solution

Solution 1

Add your solution here

Preview 0

Problem in shredding xml

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us