Solved

Insert XML into sql 2008

Posted on 2010-09-09
7
399 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
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.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 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