Read nested nodes in XML file with ASP

[Pim75]

Hello,
I have to read a XML file in ASP and save the values in a database. I
can get this work, but I cannot read some nested nodes of the xml file.

This is a part of the XML file:

<Interface>
<Product>
<CategoryFeatureGroup ID="622" No="1">
<FeatureGroup ID="0">
<Name ID="5073" Value="Technical details" langid="1" />
<Name ID="5074" Value="Technische details" langid="2" />
<Name ID="8345" Value="Détails techniques" langid="3" />
<Name ID="16114" Value="Technische Details" langid="4" />
<Name ID="20246" Value="Technical details" langid="5" />
<Name ID="24378" Value="Technical details" langid="6" />
</FeatureGroup>
</CategoryFeatureGroup>
<CategoryFeatureGroup ID="42" No="60">
<FeatureGroup ID="3">
<Name ID="4863" Value="Memory" langid="1" />
<Name ID="4864" Value="Geheugen" langid="2" />
<Name ID="7176" Value="Mémoire vive" langid="3" />
<Name ID="16018" Value="Speicher" langid="4" />
<Name ID="20150" Value="Memory" langid="5" />
<Name ID="24282" Value="Memory" langid="6" />
</FeatureGroup>
</CategoryFeatureGroup>
</Product>
</Interface>


I read the node <CategoryFeatureGroup> with the following code:

Set rootNode = xmlDoc.selectSingleNode("Interface/Product")
For Each Node in rootNode.selectNodes("CategoryFeatureGroup")
Waarde(n) =
xmlDoc.getElementsByTagName("CategoryFeatureGroup").item(n).getAttribute("ID")
n=n+1
Next

Now I also have to read the values from <FeatureGroup> and the <Name>
elements within every <CategoryFeatureGroup>. Can someone tell me how
to do this within the ASP code above?

Thanks for any help!
Nick

 

[Anthony Jones]

Hello,
I have to read a XML file in ASP and save the values in a database. I
can get this work, but I cannot read some nested nodes of the xml file.

This is a part of the XML file:

<Interface>
<Product>
<CategoryFeatureGroup ID="622" No="1">
<FeatureGroup ID="0">
<Name ID="5073" Value="Technical details" langid="1" />
<Name ID="5074" Value="Technische details" langid="2" />
<Name ID="8345" Value="Détails techniques" langid="3" />
<Name ID="16114" Value="Technische Details" langid="4" />
<Name ID="20246" Value="Technical details" langid="5" />
<Name ID="24378" Value="Technical details" langid="6" />
</FeatureGroup>
</CategoryFeatureGroup>
<CategoryFeatureGroup ID="42" No="60">
<FeatureGroup ID="3">
<Name ID="4863" Value="Memory" langid="1" />
<Name ID="4864" Value="Geheugen" langid="2" />
<Name ID="7176" Value="Mémoire vive" langid="3" />
<Name ID="16018" Value="Speicher" langid="4" />
<Name ID="20150" Value="Memory" langid="5" />
<Name ID="24282" Value="Memory" langid="6" />
</FeatureGroup>
</CategoryFeatureGroup>
</Product>
</Interface>


I read the node <CategoryFeatureGroup> with the following code:

Set rootNode = xmlDoc.selectSingleNode("Interface/Product")
For Each Node in rootNode.selectNodes("CategoryFeatureGroup")
Waarde(n) =
xmlDoc.getElementsByTagName("CategoryFeatureGroup").item(n).getAttribute("ID
")
n=n+1
Next

Now I also have to read the values from <FeatureGroup> and the <Name>
elements within every <CategoryFeatureGroup>. Can someone tell me how
to do this within the ASP code above?

Thanks for any help!
Nick

<<<<

You select each CategroyFeatureGroup into a Node variable but then don't use
the variable.

For Each Node in rootNode.selectNodes("CategoryFeatureGroup")
Waarde(n) = node.getAttribute("ID")
n=n+1
Next

Why is this data ending up in an array?
Where do you want to put the other data?

It seems to me you want to build a whole bunch of arrays of variables to
then update a DB. Which Database are you using?

A good solution would avoid loading up a set of variables and arrays but
simply take the content of the XML more directly to the DB.


Anthony

 

[Pim75]

Hello Anthony,

Thanks for your reply.
After collecting the data I want to insert in into different tables in
my MS SQL 2000 database. In my ASP script I use various sql statements
for this.

I know there's bulk load function in SQL server, but as the XML file is
a bit complex I can't get this done with bulk load. Constructing the
right xsd is pretty difficult for me, also because there are about 10
different tables where data has te be inserted.

Do you have any experience with this?
Your help is really welcome.

best regards,
Nick

 

[Anthony Jones]

Hello Anthony,

Thanks for your reply.
After collecting the data I want to insert in into different tables in
my MS SQL 2000 database. In my ASP script I use various sql statements
for this.

I know there's bulk load function in SQL server, but as the XML file is
a bit complex I can't get this done with bulk load. Constructing the
right xsd is pretty difficult for me, also because there are about 10
different tables where data has te be inserted.

Do you have any experience with this?

Yes plenty.

I suggest you take a look in SQL Server Books Online at the OPENXML
function.

In cases like this I simply pass the XML on to a SQL Server SP and do all
the work in there.

Here is the general idea:-


<%

Dim oDOM : Set oDOM = Server.CreateObject("MSXML2.DOMDocument.3.0")
oDOM.async = False
oDOM.load Request

' Code here to make any adjustments and validations of the XML

Dim sXML

sXML = oDOM.xml

Dim cmd : Set cmd = Server.CreateObject("ADODB.Command")

Set cmd.ActiveConnection = GetConn()
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "SP_Process_XML"

cmd.parameters.append cmd.createparameter("@xml", adLongVarWChar,
adParamInput, Len(sXML), sXML)

cmd.Execute

cmd.close

%>


CREATE PROCEDURE SP_Process_XML
@xml ntext,
AS

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml

INSERT tblCatFeatureGroup (tblCatFeatureGroup_ID, featNo)
SELECT [id], featNo
FROM OPENXML (@hdoc, '//CategoryFeatureGroup', 2)
WITH ([id] int '@ID', featNo int '@No') doc

INSERT tblFeatureGroupName (tblFeatureGroupName_ID, Feature_ID,
tblCatFeatureGroup_ID, [Name])
SELECT [id], featID, catID, [name]
FROM OPENXML (@hdoc, '//FeatureGroup/Name', 2)
WITH ([id] int '@ID', featID int '../@ID', catID int '../../@ID', [name]
nvarchar(50) '@Value') doc


EXEC sp_xml_removedocument @hdoc

 

[Pim75]

Tested with OPENXML and yes, this works really great
One thing... when I'm going to import more XML files after eachother,
there can be duplicate values for <category_feature_group>.

What I discovered is that all values for <category_feature_group> in
the XML file are skipped when there's one duplicate record found in the
corresponding table.

Is there a way to skip duplicate values and only add the values that
are not already in the table? At this moment I use the command:

INSERT INTO Category_feature_group (category_feature_group_id, catid,
feature_group_id, no)
SELECT *
FROM OPENXML (@index, 'ICECAT-interface/Product/CategoryFeatureGroup')
WITH (ID int, ID int '../Category/@ID', ID int 'FeatureGroup/@ID', No
int)

Thanks again!