CODEWITHSUNDEEP
xmlsql-server-2008c#-4.0
Harun
Harun

Reputation: 5179

How to effectively insert XML data to multiple tables along with the relationships in c#.net 4.0?

I have an XML file which has few nodes of similar type. Each of those node will have child nodes in them. The requirement is i need to insert the data of the Main nodes to a particular table and each of it's child table data to a particular child table with the foreign key set as the Primary key of the first insertion. The XML looks like the following sample,

       <XML>
          <Provider>
              <providerID>1</ProviderID>
              .
              .
              .
              <ContactInfo>
                 <Address>address1</Address>
                 .
                 .
              </ContactInfo>                  
          </Provider>
           <Provider>
              <providerID>2</ProviderID>
              .
              .
              .
              <ContactInfo>
                 <Address>Address2</Address>
                 .
                 .
              </ContactInfo>                 
          </Provider>

The exact requirement is i need to insert provider data to Table1 first, then i need to insert the ContactInfo within each provider to Table2 along with PK of Table1 as FK of Table2, ..............

How can implement this efficiently in c#.net avoiding complex looping? Any one please help me on this....

Upvotes: 0

Views: 1688

Answers (1)

user1499112
user1499112

Reputation: 

Declare @xml xml
Set @xml = 
'<XML>
<Provider>
    <providerID>1</providerID>
    <ContactInfo>
        <Address>address1</Address>
    </ContactInfo>                  
</Provider>

<Provider>
    <providerID>2</providerID>
    <ContactInfo>
        <Address>address2</Address>
    </ContactInfo>                  
</Provider>

</XML>'

Select 
X.T.value('(providerID)[1]', 'int'),
X.T.value('(ContactInfo/Address)[1]', 'varchar(100)')
From @xml.nodes('/XML/Provider') as X(T)

ok. Now you have got the query to extract the data from xml. Start with Inserting all these records into a temporary table.

Once done, insert into the first table. done? Now insert the join records(join the first table and temporary table to get the Primary key values from the First table).

Finally, insert the second Temporary table into your second table.

Upvotes: 1

Related Questions