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

Asked By Sajjad M
08-Aug-10 08:24 PM
Earn up to 0 extra points for answering this tough question.

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>

  re: How to read XML into SQL? Should I use LINQ to XML?

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
How to read XML into SQL? Should I use LINQ to XML? Can someone please tell me (sample code) how 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 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 QuestionList> <Title> Check box to blind drop-ship< / Title> <Display> CheckBoxList< / Display> <BooleanAnswerList> <Answer> Order will be shipped as arriving from
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
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
ASP.Net / VB.Net - DB2 Connection ASP.Net / VB.Net - DB2 Connection Hi Champs I have to start work on get data from DB2 - how can we do that in .net web application. Which one is better to chose vb / asp -DB2 or vb.net / asp.net - DB2 Please share your exp. Thx hello best
have a doubt actually what is the diffrence between ADO.NET AND VB.NET, ADO.NET AND ASP.NET, ASP.NET AND VB.NET, C#.NET AND VB.NET PLS help well i will explain you about two
a good site for learning each and every aspects of vb.net theory HI Visual Basic .NET (VB.NET or VB .NET) is a version of Microsoft's Visual Basic that was designed, as part of the company's .NET product group, to make Web services applications easier to develop. According to Microsoft, VB .NET was reengineered, rather than released as VB 6.0 with
Need to convert Project VB.Net to C#.Net Hi I am working on web application - asp.net / vb.net v1.1 and i need to convert it into asp.net / C Net . Is there any tool which i can use to convert end, you're right, it requires someone who knows both VB.Net and C#.net to complete the task. if u convert
VB Net English vs VB Net Japanese Are there any differences with VB.Net English and VB.Net Japanese? If I install VB.Net English on a Japanese OS (eg Windows 2000), will it