Solved

Insert XML into sql 2008

Posted on 2010-09-09
7
402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 63
ASP.NET Load html part first, then start function 2 38
78 files, need to delete row 2 in every file 3 31
HIghlights of SSIS? 3 42
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

734 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