locked
XML to Server 2005 - Newbie question RRS feed

  • 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

     

    Tuesday, August 28, 2007 8:34 PM

Answers

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

    Wednesday, August 29, 2007 5:19 PM

All replies

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

    Wednesday, August 29, 2007 5:19 PM
  • 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

    Wednesday, August 29, 2007 6:41 PM
  • 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.

    Wednesday, August 29, 2007 7:17 PM
  • no there are no additional C and D elements

     

    Wednesday, August 29, 2007 8:09 PM
  • In that case the code that I gave should work; give it a try and let me know if it does the job.

    Thursday, August 30, 2007 12:19 PM
  • 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

    Friday, August 31, 2007 12:00 AM
  • Is the list of all possible elements known in advance?  Does the target table contain columns for each element in the XML?

    Friday, August 31, 2007 1:30 AM
  • 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

    Friday, August 31, 2007 1:42 AM