mattegol
asked on
Insert XML into sql 2008
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></BusinessP hone>
<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...
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></BusinessP
<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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..
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..
ASKER
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
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
ASKER
I meant strSQL = strSQL & ("'" & objIDNode.Text & "'")
not strSQL = strSQL & ("'" & Val(objIDNode.Text) & "'")
not strSQL = strSQL & ("'" & Val(objIDNode.Text) & "'")
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
Set objIDNode = objXMLDoc.SelectSingleNode
Set objIDNode = objXMLDoc.SelectSingleNode
You could also Change
strSQL = strSQL & ("'" & objIDNode.Text & "'")
If objIDNode is Nothing Then
-Bear
Msgbox "Id Node not Found"
Else
strSQL = strSQL & ("'" & objIDNode.Text & "'")
End IfASKER
After tweaking it to suit my needs it works like a charm.
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.