LINQ - How to read XML into SQL? Should I use LINQ to XML?

Asked By Sajjad M
08-Aug-10 08:24 PM

Can someone please tell me (sample code) how to read this XML into a SQL DB?  Should I use LINQ to XML?  Does anyone have any sample code?

I am using VB.NET

<?xml version="1.0" encoding="utf-8"?>

<ReadOrderResponseType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<Status xmlns="urn:mysol:apis">Success</Status>

<TimeStamp xmlns="urn:mysol:apis">2010-07-25T15:26:49.1150235Z</TimeStamp>

<StoreUrl xmlns="urn:mysol:apis">http://xyz.COM</StoreUrl>

<PageResponse xmlns="urn:mysol:apis">

<Page>1</Page>

<Size>10</Size>

<TotalSize>18033</TotalSize>

<HasMore>true</HasMore>

</PageResponse>

<OrderList OrderId="20147184" OrderNumber="20100560" xmlns="urn:mysol:apis">

<Archived>false</Archived>

<CreateDate>2010-07-25T01:37:04.067Z</CreateDate>

<ReferringURL>http://www.ccc.com/Electronics/Landing1836a82496.html</ReferringURL>

<Customer>

<Browser>Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C)</Browser>

<Registered>false</Registered>

<IpAddress>98.555.555.555</IpAddress>

<EmailAddress>xsd@yahoo.com</EmailAddress>

<NonTaxable>false</NonTaxable>

<BillingAddress>

<FirstName>John</FirstName>

<LastName>Doe</LastName>

<Address1>222 Adane Road</Address1>

<City>MyCity</City>

<StateProvince>PA</StateProvince>

<PostalCode>15001</PostalCode>

<Country>US</Country>

<Phone>7245550000</Phone>

</BillingAddress>

<ShippingAddress>

<FirstName>John</FirstName>

<LastName>Doe</LastName>

<Address1>222 Adane Road</Address1>

<City>MyCity</City>

<StateProvince>PA</StateProvince>

<PostalCode>15001</PostalCode>

<Country>US</Country>

<Phone>7245550000</Phone>

</ShippingAddress>

</Customer>

<Invoice>

<Subtotal Currency="USD">11.99</Subtotal>

<Shipping Currency="USD">8.49</Shipping>

<Total Currency="USD">20.48</Total>

<CombineHandling>true</CombineHandling>

<Weight UnitOfMeasure="Imperial">

<Major>3</Major>

<Minor>0.00</Minor>

</Weight>

<LineItemList LineItemId="80927">

<FreeShipping>false</FreeShipping>

<Name>OEM Standard battery</Name>

<NonTaxable>false</NonTaxable>

<PartNumber>AB463446FZ</PartNumber>

<ProductId>82496</ProductId>

<ProductClass>Regular</ProductClass>

<QtySold>1</QtySold>

<Weight UnitOfMeasure="Imperial">

<Major>3</Major>

<Minor>0.00</Minor>

</Weight>

<UnitPrice Currency="USD">11.99</UnitPrice>

<ShippingOption>Shippable</ShippingOption>

</LineItemList>

</Invoice>

<Payment>

<AuthorizationId>248459</AuthorizationId>

<TransactionId>3100095455</TransactionId>

<CreditCard>

<Expiration>2012-05-01</Expiration>

<FirstName>John</FirstName>

<LastName>Doe</LastName>

<Number>3333</Number>

<Issuer>AmericanExpress</Issuer>

<Verification>

<Card>Match</Card>

<PostalCode>Match</PostalCode>

<Street>Match</Street>

</Verification>

</CreditCard>

<PaymentMethod>AuthorizeNet</PaymentMethod>

</Payment>

<Shipping>

<Name>Ground Service</Name>

<Carrier>Customer</Carrier>

</Shipping>

<Status OrderStatusId="1">

<Name>Order Received</Name>

</Status>

<QuestionList>

<Title>Check box to blind drop-ship</Title>

<Display>CheckBoxList</Display>

<BooleanAnswerList>

<Answer>Order will be shipped as arriving from billing name &amp; address. Slip will not be included.</Answer>

<Value>false</Value>

</BooleanAnswerList>

</QuestionList>

<QuestionList>

<Title>sales person intial</Title>

<Display>TextList</Display>

<TextAnswerList />

</QuestionList>

</OrderList>

</ReadOrderResponseType>

  Ken Fitzpatrick replied to Sajjad M
08-Aug-10 09:21 PM
Sajjad,
This XML looks fairly complex. It appears to have multiple tables and relationships. You can use SQL Bulk Loader to do it for you, but it will need a schema file that defines the table structure and relationships in the XML file or SQL bulk Loader will not be able to load it. The following websites have some details on how to do it:
http://support.microsoft.com/kb/316005/en-us
http://msdn.microsoft.com/en-us/library/ms171806.aspx
The second website is more related to your situation and will help you with the schema file, but make sure you look at them both.

Hope this helps,

Ken
Create New Account
help
Difference between vb and vb.net Hai, I want to know about the main difference between vb and vb.net. • The greatest change in VB6 and VB.NET is of runtime environment. VB6 used the VB-Runtime while VB.NET uses the .Net Common Language Runtime (.Net CLR). The CLR is much better designed and
vb.net hi what is VB.net ?and benefits of vb.net what is asp.net and benefits of asp.net pls help me hi, what is VB.net ?and benefits of vb.net Visual Basic .NET (VB.NET or VB .NET) is a
Convert .NET Framework Hi Im new to vb.net. Can we convert vb6 forms, projects in to vb.net VB.NET Discussions VB.NET (1) VB (1) Ligthert (1) Wagner (1) Seth (1) Java (1) Rowe (1) Vb6 (1
Is VB.NET dead? .NET Framework Dear VB.NET Team, you did a wonderful job in making VB.NET a full fledged language, at least on the compiler side. When teaching students I always see that solving labs using VB.NET gives a faster result than C#. However the "distribution chain" to your customers is totally