ExamGuru IT/.Net Guru : XML to SQL problem on ASPFriends.com 'aspngxml' list

.NETGURU

XML to SQL problem

Messages

Related Types

This message was discovered on ASPFriends.com 'aspngxml' list.

Bob Herrmann

Hi,

I have been trying to take a xml file and dump it into a SQL Server 2000 table without much luck. This is my first attempt working with xml so I may be way off base. Here is my code:

If reader.NodeType = XmlNodeType.EndElement Then
If reader.Name.ToLower() = "tabCatalog" Then
Dim FVArray As String() = AddSeparator(fieldNamesValues, ","c)
Dim fields As String = FVArray(0)
Dim fieldVals As String = FVArray(1)
Dim oConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("BOBSQL"))
Dim sSql As String = "INSERT INTO tabCatalog (" + fields + ") VALUES (" + fieldVals + ")"
Dim oComm As SqlCommand = New SqlCommand(sSql, oConn)
oConn.Open()
oComm.ExecuteNonQuery()
oConn.Close()
fieldNamesValues.Clear()
End If
End If

I have been debugging this code and I have determined that the second IF statement is always false. I inserted a response.write(reader.name.tolower()) and I get a list of tags including tabcatalog so this code should execute. Here is a sample of my XML file:

- <XMLCatalog>
- <tabCatalog>
<ID>205753</ID>
<Desc1>ANANDATSE DOHI (CASSETTE)</Desc1>
<Price>7.95</Price>
<ImageFilename>thumbs/t105120.jpg</ImageFilename>
<PriceB4Disc>0</PriceB4Disc>
<CatalogID>1</CatalogID>
</tabCatalog>
<tabCatalog>
<ID>205755</ID>
<Desc1>Test CD</Desc1>
<Price>9.95</Price>
<ImageFilename>thumbs/t105125.jpg</ImageFilename>
<PriceB4Disc>0</PriceB4Disc>
<CatalogID>2</CatalogID>
</tabCatalog>
</XMLCatalog>

I was trying to INSERT a record into my SQL table whenever I encounter the </tabcatalog> tag since this is the end of my XML record. Is this the best way to this? If so, then does anyone know why its not working? Like I said, this is my first foray into XML.

Thanks,
Bob

Reply to this message...

Thacker, Jason J

Have you thought about just having a stored proc that takes the XML Doc as a
string (TEXT/NTEXT) and use OPENXML within your stored proc to have the data
inserted?

-jason

-----Original Message-----
From: Bob Herrmann [mailto:Click here to reveal e-mail address]
Sent: Thursday, July 18, 2002 5:39 AM
To: aspngxml
Subject: [aspngxml] XML to SQL problem

Hi,

I have been trying to take a xml file and dump it into a SQL Server 2000
table without much luck. This is my first attempt working with xml so I may
be way off base. Here is my code:

If reader.NodeType = XmlNodeType.EndElement Then
If reader.Name.ToLower() = "tabCatalog" Then
Dim FVArray As String() = AddSeparator(fieldNamesValues, ","c)
Dim fields As String = FVArray(0)
Dim fieldVals As String = FVArray(1)
Dim oConn As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("BOBSQL"))
Dim sSql As String = "INSERT INTO tabCatalog (" + fields + ") VALUES ("
+ fieldVals + ")"
Dim oComm As SqlCommand = New SqlCommand(sSql, oConn)
oConn.Open()
oComm.ExecuteNonQuery()
oConn.Close()
fieldNamesValues.Clear()
End If
End If

I have been debugging this code and I have determined that the second IF
statement is always false. I inserted a
response.write(reader.name.tolower()) and I get a list of tags including
tabcatalog so this code should execute. Here is a sample of my XML file:

<file:///C:/Bob/BobXmlDoc.xml#> - <XMLCatalog>
<file:///C:/Bob/BobXmlDoc.xml#> - <tabCatalog>
<ID>205753</ID>
<Desc1>ANANDATSE DOHI (CASSETTE)</Desc1>
<Price>7.95</Price>
<ImageFilename>thumbs/t105120.jpg</ImageFilename>
<PriceB4Disc>0</PriceB4Disc>
<CatalogID>1</CatalogID>
</tabCatalog>
<tabCatalog>
<ID>205755</ID>
<Desc1>Test CD</Desc1>
<Price>9.95</Price>
<ImageFilename>thumbs/t105125.jpg</ImageFilename>
<PriceB4Disc>0</PriceB4Disc>
<CatalogID>2</CatalogID>
</tabCatalog>
</XMLCatalog>

I was trying to INSERT a record into my SQL table whenever I encounter the
</tabcatalog> tag since this is the end of my XML record. Is this the best
way to this? If so, then does anyone know why its not working? Like I
said, this is my first foray into XML.

Thanks,
Bob
| [aspngxml] member Click here to reveal e-mail address = YOUR ID
| http://www.asplists.com/asplists/aspngxml.asp = JOIN/QUIT
| http://www.asplists.com/search = SEARCH Archives

Reply to this message...

Kirk Allen Evans

MessageYou are comparing with the result of the tolower function, but the
string you are comparing against has a capital letter in it. It should
always evaluate to false. Instead, try evaluating against a string with all
lower-case letters.

Kirk Allen Evans
http://www.xmlandasp.net
"XML and ASP.NET", New Riders Publishing
http://www.amazon.com/exec/obidos/ASIN/073571200X

-----Original Message-----
From: Thacker, Jason J [mailto:Click here to reveal e-mail address]
Sent: Thursday, July 18, 2002 12:37 PM
To: aspngxml
Subject: [aspngxml] RE: XML to SQL problem

Have you thought about just having a stored proc that takes the XML Doc as
a string (TEXT/NTEXT) and use OPENXML within your stored proc to have the
data inserted?

-jason

-----Original Message-----
From: Bob Herrmann [mailto:Click here to reveal e-mail address]
Sent: Thursday, July 18, 2002 5:39 AM
To: aspngxml
Subject: [aspngxml] XML to SQL problem

Hi,

I have been trying to take a xml file and dump it into a SQL Server 2000
table without much luck. This is my first attempt working with xml so I may
be way off base. Here is my code:

If reader.NodeType = XmlNodeType.EndElement Then
If reader.Name.ToLower() = "tabCatalog" Then
Dim FVArray As String() = AddSeparator(fieldNamesValues, ","c)
Dim fields As String = FVArray(0)
Dim fieldVals As String = FVArray(1)
Dim oConn As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("BOBSQL"))
Dim sSql As String = "INSERT INTO tabCatalog (" + fields + ") VALUES
(" + fieldVals + ")"
Dim oComm As SqlCommand = New SqlCommand(sSql, oConn)
oConn.Open()
oComm.ExecuteNonQuery()
oConn.Close()
fieldNamesValues.Clear()
End If
End If

I have been debugging this code and I have determined that the second IF
statement is always false. I inserted a
response.write(reader.name.tolower()) and I get a list of tags including
tabcatalog so this code should execute. Here is a sample of my XML file:

- <XMLCatalog>
- <tabCatalog>
<ID>205753</ID>
<Desc1>ANANDATSE DOHI (CASSETTE)</Desc1>
<Price>7.95</Price>
<ImageFilename>thumbs/t105120.jpg</ImageFilename>
<PriceB4Disc>0</PriceB4Disc>
<CatalogID>1</CatalogID>
</tabCatalog>
<tabCatalog>
<ID>205755</ID>
<Desc1>Test CD</Desc1>
<Price>9.95</Price>
<ImageFilename>thumbs/t105125.jpg</ImageFilename>
<PriceB4Disc>0</PriceB4Disc>
<CatalogID>2</CatalogID>
</tabCatalog>
</XMLCatalog>

I was trying to INSERT a record into my SQL table whenever I encounter the
</tabcatalog> tag since this is the end of my XML record. Is this the best
way to this? If so, then does anyone know why its not working? Like I
said, this is my first foray into XML.

Thanks,
Bob
| [aspngxml] member Click here to reveal e-mail address = YOUR ID
| http://www.asplists.com/asplists/aspngxml.asp = JOIN/QUIT
| http://www.asplists.com/search = SEARCH Archives
| [aspngxml] member Click here to reveal e-mail address = YOUR ID
| http://www.asplists.com/asplists/aspngxml.asp = JOIN/QUIT
| http://www.asplists.com/search = SEARCH Archives

Reply to this message...

Tim Curtin

This works:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim i as Integer
dim oDoc as New XmlDataDocument
dim item as XmlElement
dim items as XmlNodeList
dim conString as String="data source=localhost;user
id=sa;password=1aspnet;initial catalog=test"
dim con as New SqlConnection(conString)
con.Open
dim da as New SqlDataAdapter

odoc.Load("c:\inetpub\wwwroot\throwaway\TestDotNetToUpdateSQL.xml")
items = odoc.SelectNodes("//tabCatalog")
for each item in items
dim cmd as New SqlCommand
with cmd
.CommandText="pSaveCatalogItem"
.CommandType=CommandType.StoredProcedure
.Connection=con
.Parameters.Add(new
SqlParameter("@ID",item.SelectSingleNode("ID").InnerText ))
.Parameters.Add(new
SqlParameter("@Desc1",ITEM.SelectSingleNode("Desc1").InnerText))
.Parameters.Add(new SqlParameter("@Price", SqlDbType.Money
,8))

.Parameters("@Price").Value=item.SelectSingleNode("Price").InnerText
.Parameters.Add(new
SqlParameter("@ImageFileName",item.SelectSingleNode("ImageFilename").InnerText))
.Parameters.Add(new SqlParameter("@PriceB4Disc"
,sqldbtype.money,8))

.Parameters("@PriceB4Disc").Value=item.SelectSingleNode("PriceB4Disc").InnerText
.Parameters.Add(new
SqlParameter("@CatalogID",item.SelectSingleNode("CatalogID").InnerText ))
.ExecuteNonQuery
cmd=nothing
i+=1
end with
Next
response.Write(i & " XML Records were inserted")
End Sub

[Original message clipped]

_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

Reply to this message...

Tim Curtin

This is even cleaner:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim i as Integer
dim oDoc as New XmlDataDocument
dim item as XmlElement
dim items as XmlNodeList
dim con as New
SqlConnection(ConfigurationSettings.AppSettings("ConString").ToString())
con.Open
dim da as New SqlDataAdapter
odoc.Load(ConfigurationSettings.AppSettings("xmlfile").ToString())
items = odoc.SelectNodes("//tabCatalog")
dim cmd as New SqlCommand
with cmd
.CommandText="pSaveCatalogItem"
.CommandType=CommandType.StoredProcedure
.Connection=con
.Parameters.Add(new SqlParameter("@ID", sqldbtype.Int))
.Parameters.Add(new
SqlParameter("@Desc1",sqldbtype.VarChar,200))
.Parameters.Add(new SqlParameter("@Price", SqlDbType.Money ))
.Parameters.Add(new
SqlParameter("@ImageFileName",sqldbtype.VarChar,100))
.Parameters.Add(new SqlParameter("@PriceB4Disc"
,sqldbtype.money,8))
.Parameters.Add(new SqlParameter("@CatalogID",sqldbtype.Int))
for each item in items

.Parameters("@ID").Value=item.SelectSingleNode("ID").InnerText

.Parameters("@Desc1").value=item.SelectSingleNode("Desc1").InnerText

.Parameters("@Price").Value=item.SelectSingleNode("Price").InnerText

.Parameters("@ImageFileName").Value=item.SelectSingleNode("ImageFilename").InnerText

.Parameters("@PriceB4Disc").Value=item.SelectSingleNode("PriceB4Disc").InnerText

.Parameters("@CatalogID").Value=item.SelectSingleNode("CatalogID").InnerText
.ExecuteNonQuery
i+=1
Next
end with
cmd=nothing
con.Close
con=nothing
response.Write(i & " XML Records were inserted")
End Sub

[Original message clipped]

_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail.
http://www.hotmail.com

Reply to this message...

Bob Herrmann

Tim,

Can you show the stored procedure you use with this code?

Thanks,
Bob
----- Original Message -----
From: "Tim Curtin" <Click here to reveal e-mail address>
To: "aspngxml" <Click here to reveal e-mail address>
Sent: Friday, July 19, 2002 8:24 AM
Subject: [aspngxml] Re: XML to SQL problem

[Original message clipped]

nnerText
[Original message clipped]

Text
[Original message clipped]

Reply to this message...

Tim Curtin

Bob,
1.Run this proc in SQL to create the table and SP. You may have to modify
it:
CREATE PROCEDURE dbo.pSaveCatalogItem

    (
        @ID bigint,
        @Desc1 varchar(200),
        @Price money,
        @ImageFileName varchar(100),
        @PriceB4Disc money,
        @CatalogID int
    )
AS
    If Exists(Select ID From catalog Where ID=@ID)
        Begin
        Update Catalog Set
        Desc1=@Desc1,
        Price=@Price,
        ImageFileName=@ImageFileName,
        PriceB4Disc=@PriceB4Disc,
        CatalogID=@CatalogID
        Where ID=@ID
        End
    Else
        Insert Catalog
        Values(@ID,@Desc1,@Price,@ImageFileName,@PriceB4Disc,@CatalogID)

    RETURN

GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[catalog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[catalog]
GO

CREATE TABLE [dbo].[catalog] (
    [ID] [bigint] NOT NULL ,
    [Desc1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Price] [money] NOT NULL ,
    [ImageFileName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
    [PriceB4Disc] [money] NOT NULL ,
    [CatalogID] [int] NOT NULL
) ON [PRIMARY]
GO

[Original message clipped]

_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

Reply to this message...

Bob Herrmann

Thanks Tim,

I do have my program working now....sort of. I can INSERT records into my
SQL database table. But what about when my customer sends me an XML file
and not all of the elements are there. For instance, let's say I receive an
XML file but some of the nodes have no Desc1 tag? My program bombs on this
condition. What is the best way to check for this? Also, is my terminology
correct for nodes and elements? I am brand new to XML. I have been using
.Net for awhile this is my first XML-based project.

Thanks again,
Bob
----- Original Message -----
From: "Tim Curtin" <Click here to reveal e-mail address>
To: "aspngxml" <Click here to reveal e-mail address>
Sent: Friday, July 19, 2002 12:36 PM
Subject: [aspngxml] Re: XML to SQL problem

[Original message clipped]

Reply to this message...

Kirk Allen Evans

You can validate the incoming XML document against a schema. You can also
provide the schema to the developer generating the XML source, indicating
that the data is not valid unless it can be validated.

Kirk Allen Evans
http://www.xmlandasp.net
"XML and ASP.NET", New Riders Publishing
http://www.amazon.com/exec/obidos/ASIN/073571200X

[Original message clipped]

Reply to this message...

Bob Herrmann

How do I do this validation? Can you provide me an example? I have the
schema for the xml file. It looks like there are elements with values
sometimes and other times there are no values. For example:

<Catalog>
<Item>

<ID>123</ID><Desc1>aaaaaaaaaaaa</Desc1><Price>11.95</><Desc4>bbbbbbbbb</Desc
4><Qty>3</Qty>
</Item>
<Item>
<ID>456</ID><Desc1>xxxxxxxxxxxx</Desc1><Price>15.95</><Qty>1</Qty>
</Item>
</Catalog>

Notice there is no Desc4 in the second row. This causes my program to fail.

Thanks,
Bob
----- Original Message -----
From: "Kirk Allen Evans" <Click here to reveal e-mail address>
To: "aspngxml" <Click here to reveal e-mail address>
Sent: Friday, July 19, 2002 3:09 PM
Subject: [aspngxml] Re: XML to SQL problem

[Original message clipped]

Reply to this message...

Tim Curtin

Use a Schema to validate the XML file. You and your customer MUST have a
contract of the data coming in. If the XML references a schema at the root
node, then the XML parser will validate it as it is being loaded and the
load method will return false. Actually, if you restate the load method to:
If Not oDoc.load("path") then
response.write(oDoc.ParseError.Reason)
'logXMLError
else
'process XML
end if

[Original message clipped]

_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com

Reply to this message...

Tim Curtin

Bob,
If the ID element can have more than 1 <Desc> element, then I would make
them unique by adding an id attribute to each one:
<item>
<ID>1234</ID>
<Desc id="1">asdfasdf</Desc>
<Desc id="2">poiupio</Desc>
</item>
That way you can use a for loop to do a for each desc in descs like my prior
example for item in items. Then check for the item.tagName="Desc4". If it is
then, add to the loop that performs the ADO Command update. The xmlns: is
required. the xsi is the url to your schema.

The schema is attached at the root node:
<?xml version="1.0"?>
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:noNamespaceSchemaLocation="mySchema.xsd">
<tagCatalog>

</tagCatalog
</root>
I would recommend Dan Wahlin's XML for ASP.Net book. Dan actually is
currently contributing on one of the other threads. It sounds like you need
to do a little research on Schemas.

[Original message clipped]

_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail.
http://www.hotmail.com

Reply to this message...

Kirk Allen Evans

[Original message clipped]

Actually, this input is not even well-formed XML. If you are using a .NET
parser, you should catch an error because the Load method would fail. If
you are using MSXML, then you can check the document.parseError.errorCode
property to see if it is non-zero: if it is, then a parsing error occurred.
Likewise, the Load method will return a Boolean false if the document could
not be properly parsed.

Here is the "fixed" input document. Notice that the Price element now has a
proper closing tag:

<Catalog>
    <Item>
        <ID>123</ID>
        <Desc1>aaaaaaaaaaaa</Desc1>
        <Price>11.95</Price>
        <Desc4>bbbbbbbbb</Desc4>
        <Qty>3</Qty>
    </Item>
    <Item>
        <ID>456</ID>
        <Desc1>xxxxxxxxxxxx</Desc1>
        <Price>15.95</Price>
        <Qty>1</Qty>
    </Item>
</Catalog>

A separate problem is the missing "Desc4" element in the second item. For
this, you can use a schema to validate against. There is a lot of
information in the MSXML 4.0 SDK about how to perform validations (see the
SchemaCollection), and there is a lot of information on this for .NET as
well (see ValidatingReader). If you still need help, post and I am sure
someone will post a snippet of validation code.

The main part that you will need to grasp is the schema itself. The easiest
way is to use XMLSpy (search Google for download links) to create the
schema. You can also type it by hand, but that is a bit more involved.
Here is a stab at one. You should only need to reference the
targetNamespace URI in your input document using
xmlns="http://tempuri.org/XMLSchema1.xsd";.

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="XMLSchema1"
targetNamespace="http://tempuri.org/XMLSchema1.xsd";
elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema1.xsd";
xmlns:mstns="http://tempuri.org/XMLSchema1.xsd";
xmlns:xs="http://www.w3.org/2001/XMLSchema";>
    <xs:complexType name="ItemType">
        <xs:sequence maxOccurs="1" minOccurs="1">
            <xs:element name="ID" type="xs:long" minOccurs="1" maxOccurs="1" />
            <xs:element name="Desc1" type="xs:string" minOccurs="1" maxOccurs="1" />
            <xs:element name="Price" type="xs:float" minOccurs="1" maxOccurs="1" />
            <xs:element name="Desc4" type="xs:string" minOccurs="1" maxOccurs="1" />
            <xs:element name="Qty" type="xs:int" minOccurs="1" maxOccurs="1" />
        </xs:sequence>
    </xs:complexType>
    <xs:element name="Catalog">
        <xs:complexType>
            <xs:sequence minOccurs="1" maxOccurs="unbounded">
                <xs:element name="Item" type="ItemType" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

FWIW, I have received huge praise for the chapter on schemas in my book. It
also contains lots of information on both .NET XML parsing as well as using
MSXML.

Kirk Allen Evans
http://www.xmlandasp.net
"XML and ASP.NET", New Riders Publishing
http://www.amazon.com/exec/obidos/ASIN/073571200X

Reply to this message...

Bob Herrmann

Thanks for your help Kirk. The missing </Price> tag was just a keying error
on my part. Actually, a schema file came with the xml file. I need help
with the validation though. I cannot seem to find an example. I want to
take my xml file and dump it into my SQL table. I have this working to a
degree. The problem is various elements are not always present. They are
not always required. You mentioned using the schema to validate the xml
file but I am at a loss on how to do this. I am really confused on how the
xml file ties to the schema file. I am new to xml and I have no books or
manuals on xml yet.

Thanks,
Bob
----- Original Message -----
From: "Kirk Allen Evans" <Click here to reveal e-mail address>
To: "aspngxml" <Click here to reveal e-mail address>
Sent: Friday, July 19, 2002 9:12 PM
Subject: [aspngxml] Re: XML to SQL problem

[Original message clipped]

Reply to this message...

Kirk Allen Evans

See the docs for XmlValidatingReader: there are examples included with it
that are fairly straightforward. Also, check xmlforasp.net: I didn't
immediately see any examples of XmlValidatingReader on there, but I am sure
dan has at least one.

Dan: do you have a link for an example using XmlValidatingReader?

Kirk Allen Evans
http://www.xmlandasp.net
"XML and ASP.NET", New Riders Publishing
http://www.amazon.com/exec/obidos/ASIN/073571200X

[Original message clipped]

Reply to this message...

	System.Configuration.ConfigurationSettings
	System.Data.CommandType
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlDataAdapter
	System.Data.SqlClient.SqlParameter
	System.Data.SqlDbType
	System.EventArgs
	System.Object
	System.Xml.XmlDataDocument
	System.Xml.XmlElement
	System.Xml.XmlNodeList
	System.Xml.XmlNodeType
	System.Xml.XmlValidatingReader

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Ad

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification