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>
<CategoryFeatur eGroup ID="622" No="1">
<FeatureGroup ID="0">
<Name ID="5073" Value="Technica l details" langid="1" />
<Name ID="5074" Value="Technisc he details" langid="2" />
<Name ID="8345" Value="Détails techniques" langid="3" />
<Name ID="16114" Value="Technisc he Details" langid="4" />
<Name ID="20246" Value="Technica l details" langid="5" />
<Name ID="24378" Value="Technica l details" langid="6" />
</FeatureGroup>
</CategoryFeature Group>
<CategoryFeatur eGroup 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>
</CategoryFeature Group>
</Product>
</Interface>
I read the node <CategoryFeatur eGroupwith the following code:

Set rootNode = xmlDoc.selectSi ngleNode("Inter face/Product")
For Each Node in rootNode.select Nodes("Category FeatureGroup")
Waarde(n) =
xmlDoc.getEleme ntsByTagName("C ategoryFeatureG roup").item(n). getAttribute("I D")
n=n+1
Next

Now I also have to read the values from <FeatureGroupan d the <Name>
elements within every <CategoryFeatur eGroup>. Can someone tell me how
to do this within the ASP code above?

Thanks for any help!
Nick

[Anthony Jones]

"Pim75" <p.******@tisca li.nlwrote in message
news:11******** **************@ k70g2000cwa.goo glegroups.com.. .

>>>>

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>
<CategoryFeatur eGroup ID="622" No="1">
<FeatureGroup ID="0">
<Name ID="5073" Value="Technica l details" langid="1" />
<Name ID="5074" Value="Technisc he details" langid="2" />
<Name ID="8345" Value="Détails techniques" langid="3" />
<Name ID="16114" Value="Technisc he Details" langid="4" />
<Name ID="20246" Value="Technica l details" langid="5" />
<Name ID="24378" Value="Technica l details" langid="6" />
</FeatureGroup>
</CategoryFeature Group>
<CategoryFeatur eGroup 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>
</CategoryFeature Group>
</Product>
</Interface>
I read the node <CategoryFeatur eGroupwith the following code:

Set rootNode = xmlDoc.selectSi ngleNode("Inter face/Product")
For Each Node in rootNode.select Nodes("Category FeatureGroup")
Waarde(n) =
xmlDoc.getEleme ntsByTagName("C ategoryFeatureG roup").item(n). getAttribute("I D
")
n=n+1
Next

Now I also have to read the values from <FeatureGroupan d the <Name>
elements within every <CategoryFeatur eGroup>. Can someone tell me how
to do this within the ASP code above?

Thanks for any help!
Nick

<<<<

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

For Each Node in rootNode.select Nodes("Category FeatureGroup")
Waarde(n) = node.getAttribu te("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]

"Pim75" <p.******@tisca li.nlwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .

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.CreateOb ject("MSXML2.DO MDocument.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.CreateOb ject("ADODB.Com mand")

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

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

cmd.Execute

cmd.close

%>
CREATE PROCEDURE SP_Process_XML
@xml ntext,
AS

DECLARE @hdoc int

EXEC sp_xml_prepared ocument @hdoc OUTPUT, @xml

INSERT tblCatFeatureGr oup (tblCatFeatureG roup_ID, featNo)
SELECT [id], featNo
FROM OPENXML (@hdoc, '//CategoryFeature Group', 2)
WITH ([id] int '@ID', featNo int '@No') doc

INSERT tblFeatureGroup Name (tblFeatureGrou pName_ID, Feature_ID,
tblCatFeatureGr oup_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_removedo cument @hdoc

Your help is really welcome.

best regards,
Nick

[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_featu re_group>.

What I discovered is that all values for <category_featu re_groupin
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_featur e_group (category_featu re_group_id, catid,
feature_group_i d, no)
SELECT *
FROM OPENXML (@index, 'ICECAT-interface/Product/CategoryFeature Group')
WITH (ID int, ID int '../Category/@ID', ID int 'FeatureGroup/@ID', No
int)

Thanks again!