Solved

Insert XML into sql 2008

Posted on 2010-09-09
7
398 Views
Last Modified: 2012-05-10
Hi i have an dynamic XML string that i want to insert into my ABC table in my database.
I need to loop through my XML string and find <ABC> and send Number, Status and Quantity into the ABC table one by one and each will also have the ID from <ID>

The string can look like this:
<PARENT>
 <ID>1A</ID>
 <RL>ABC123</RL>
 <Name>
  <FirstName>John MR</FirstName>
  <LastName>Smith</LastName>
 </Name>
 <ContactDetails>
  <Email></Email>
  <MobilePhone></MobilePhone>
  <BusinessPhone></BusinessPhone>
  <HomePhone></HomePhone>
 </ContactDetails>
 <Segments>
  <ABC>
   <Number>1</Number>
   <Status>XX</Status>
   <Quantity>1</Quantity>
  </ABC>
  <DEF>
   <Number>4</Number>
   <Status>HK</Status>
   <Quantity>1</Quantity>
  </DEF>
  <ABC>
   <Number>2</Number>
   <Status>AA</Status>
   <Quantity>1</Quantity>
  </ABC>
  <DEF>
   <Number>5</Number>
   <Status>HK</Status>
   <Quantity>1</Quantity>
  </DEF>
  <ABC>
   <Number>3</Number>
   <Status>YY</Status>
   <Quantity>2</Quantity>
  </ABC>
 </Segments>
</PARENT>

This is what I want to achieve
http://www.parkersoft.co.uk/forum/forum_posts.asp?TID=6555&title=extracting-multiple-items-from-emails
But I need to tweak it to fit my XML style...
0
Comment
Question by:mattegol
  • 4
  • 2
7 Comments
 
LVL 2

Expert Comment

by:nekkantisurendra
ID: 33639700
Hi,
   I am not sure if you want to insert XML file into a table. If so you can use XML datatype where you can store the entire XML file in one column.
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
ID: 33643648
We may need more details to really help you through this.  Where is the XML information coming from?  Are you wanting .net or vbscript (you have both tagged)?  Will there be only one ID Node per document?  

I would put the xml data in a Dom Document and loop through the nodes writing out the information as I loop.  Some base code is below to help you get started.

-Bear



' ****

' You will need to get you data into the Dom Document somehow

' ****

Dim objXMLDoc, objConn, strSQL, strSQL2

Dim objABCNodes, objABCNode, objIDNode, objChildNodes, objChildNode



strString = "<PARENT><ID>1A</ID><RL>ABC123</RL><Name><FirstName>John MR</FirstName><LastName>Smith</LastName></Name><ContactDetails> <Email></Email><MobilePhone></MobilePhone><BusinessPhone></BusinessPhone><HomePhone></HomePhone></ContactDetails><Segments><ABC> <Number>1</Number><Status>XX</Status><Quantity>1</Quantity></ABC><DEF>   <Number>4</Number>   <Status>HK</Status>  <Quantity>1</Quantity>  </DEF>  <ABC>   <Number>2</Number>   <Status>AA</Status>   <Quantity>1</Quantity>  </ABC>  <DEF>  <Number>5</Number>   <Status>HK</Status>   <Quantity>1</Quantity>  </DEF>  <ABC>   <Number>3</Number>   <Status>YY</Status>  <Quantity>2</Quantity>  </ABC> </Segments></PARENT>"



Set objXMLDoc = CreateObject("Msxml2.DOMDocument") 

Set objConn = CreateObject("ADODB.Connection")



' Connect to the Database

objConn.ConnectionString = "DSN=DSNNAME;"

objconn.Open



'objXMLDoc.Load "E:\myfile.xml"

' Smaple loading from a strin

objXMLDoc.LoadXML strString



' Start Building Sequel string

strSQL = "Insert Into MyTable ([ID], [Number], [Status], [Quantity]) Values ("



' Find All ID Nodes

Set objIDNode = objXMLDoc.SelectSingleNode("//ID")

strSQL = strSQL & ("'" & objIDNode.Text & "'")



Set objABCNodes = objXMLDoc.SelectNodes("//ABC")

If Not objABCNodes is nothing Then

	For each objABCNode in objABCNodes

		strSQL2 = ""

		Set objChildNodes = objABCNode.ChildNodes

		For Each objChildNode in objChildNodes

			strSQL2 = strSQL2 & (",'" & objChildNode.Text & "'")

		Next

		' Add Final )

		strSQL2 = strSQL2 & ")"

		

		'Write to DB

		msgbox strSQL & strSQL2

		objConn.Execute strSQL & strSQL2, adCmdText

	Next

End If



objConn.Close



msgbox "done"

Open in new window

0
 

Author Comment

by:mattegol
ID: 33644338
My bad, no vbscript.

The XML is coming from an email.

There is only one ID node.

Yes Bear that is what I'm looking for and will get me started for sure..
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:mattegol
ID: 33644668
I'm getting the error: object var is Nothing here
strSQL = strSQL & ("'" & Val(objIDNode.Text) & "'")

I'm getting the xml from an email, I use Email2DB. Here is information on how to do what I need, see top message

http://www.parkersoft.co.uk/forum/forum_posts.asp?TID=6555&title=extracting-multiple-items-from-emails
0
 

Author Comment

by:mattegol
ID: 33644700
I meant strSQL = strSQL & ("'" & objIDNode.Text & "'")

not strSQL = strSQL & ("'" & Val(objIDNode.Text) & "'")
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 33646407
That means that somehow it did not find the ID node.  The SelectNodes / SelectSingleNodes is case sensitive.  Based on your link it looks like ID may actuall be Id.  Try changing

Set objIDNode = objXMLDoc.SelectSingleNode("//ID")
To


Set objIDNode = objXMLDoc.SelectSingleNode("//Id")

You could also Change


strSQL = strSQL & ("'" & objIDNode.Text & "'")

To

If objIDNode is Nothing Then
Msgbox "Id Node not Found"
Else
strSQL = strSQL & ("'" & objIDNode.Text & "'")
End If

-Bear
0
 

Author Closing Comment

by:mattegol
ID: 33669979
After tweaking it to suit my needs it works like a charm.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 50
SQL Statement to Update Email Domain 2 23
MSSQL Speen Degradation 4 10
Crystal reports vb.net 2 16
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now