Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insert XML into sql 2008

Posted on 2010-09-09
7
Medium Priority
?
405 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 2000 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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