XML to Server 2005 - Newbie question

Question

Hi

 

I m new to the forum and to Server 2005

 

I have to transfer data from large XML files to Server 2005.I need to have the value from each text node as a row.

so here is a sample XML

 

<?xml  ......>

<A>

   <B time = 1>

       <C> Value = something </C>

       <D> Value = something</D> 

      ......

  </B>

</A>

 

<A>

   <B time = 2>

       <C> Value = something </C>

       <D> Value = something</D> 

      ......

  </B>

</A>

 

 

What i want to do is have columns in Server 2005 table named B,C and D and store each time as a row in those coloumns . i want tp store just the value not the whole XML.

 

I am sure this is really basic .Can some one give me some direction as to how to do this

 

Thanks

 

~ Hem

 

Answer 1

Here is a simplistic query provide that there are not multiple C and D entries per B entry:

 

Code Snippet

declare @testXml xml
set @testXml =
'<Root>
<A>
   <B time="1" >
       <C>something C1</C>
       <D>something D1</D> 
  </B>
</A>
 
<A>
   <B time="2">
       <C>something C2</C>
       <D>something D2</D> 
 </B>
</A>
</Root>'

 

select t.value ('./@time', 'varchar(9)') as B,
       t.value ('./C[1]',  'varchar(15)') as C,
       t.value ('./D[1]',  'varchar(15)') as D
 from @testXml.nodes('/Root/A/B') as x(t)

 

/*
B         C               D
--------- --------------- ---------------
1         something C1    something D1
2         something C2    something D2
*/

 

 

I guess my question could be can you give a little more details of this piece of the structure:

   

Code Snippet

   <B time="2">
       <C>something C2</C>
       <D>something D2</D>  
   ...
   </B>

 

 

Are there (1) additional elements besideds C and D and (2) are there additional C and D elements within each B element?

Answer 2

Hi

 

Thanks for replying

 

Yes there are additional elements with B .There are close to 12 additional elements and one of them and one of them has childs

 

<b>

    <c> something = value </c>

   <d> something = value </d>

  ......

 <m>

      <m1> something = value</m1>

     <m2> something = value</m2>

     <m3> something = value</m3>

</m>

</b>

 

 

Hope i answered your questions

Answer 3

Again, the more important question to me is "are there additiona C and D elements (more than 1) with B elements".  If so, how are those structured.

Answer 4

no there are no additional C and D elements

 

Answer 5

In that case the code that I gave should work; give it a try and let me know if it does the job.

Answer 6

hey

 

thanks for your help

 

I was able to get it working

 

But is there a way that i can make the code intelligent

 

Instead of specifically giving it the elements name (C, D ...) it looks for elements and inserts them in the column corresponding to that element

 

My point is that the code is working but if the element name is change or a new element is added or one removed , some one has to change the code.Can it be more intelligent

 

Thanks again for all your help

Answer 7

Is the list of all possible elements known in advance?  Does the target table contain columns for each element in the XML?

Answer 8

Hi

 

So this is the scenario

 

I get XML files everyday from some one and i run the stored procedure with the above code to create a new databse everyday and so the data is entered in a new table everyday.Now if i hardcore the elements name and the XML that i get is changed my code would fail.So i wanted to make it intelligent so that it adjusts to the change in XML

 

Let me know if I am not clear and I will try explaining it again

 

Again thanks for your help