Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Insert XML into sql 2008

Posted on 2010-09-09
7
Medium Priority
?
406 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
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.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Integration Management Part 2
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 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