xml object

[J.D.]

I have an xml data that I want to store in a database.

I want the following fields
1. Account-id
2. order-number
3. first-name
4. last-name
5. address-line
6. address-line-2
7 city
8, state
9 postal-code
10 total-points-spent
11 the list of items that were purchased which are in the section
item-redeemed


And this is the asp code that I use to move through the list. right now all
I can get is the redemption top-level, but I cannot seem to go down to the
subitems. Can anyone help out here and help get this into a asp script.
Thanks.

set root = xmlDom.documentElement set orderheaders =
root.selectNodes("redemption") for counter=0 to orderheaders.length-1 for
each header in orderheaders.item(counter).childnodes 'set subheader =
header.selectnodes("item-redeemed") 'for counter1=0 to subheader.length-1
'for each itemhead in subheader.item(counter1).childnodes response.Write "
" & header.nodename & "=" & header.text select case header.nodename case
"total-points-spent" response.Write "Points spent = " & header.text & "
" end select 'next 'next next next set orderheaders=nothing set root =
nothing


<customer-redemption>
<partner-client>someid</partner-client>
<partner-client-participant-id>
<account-id>F4B62207-3</account-id>
<user-name>3</user-name>
<password>yeahyeah</password>
</partner-client-participant-id>
<redemption>
<order-number>213387</order-number>
<ship-to>
<first-name>J</first-name>
<last-name>D</last-name>
<address-line-1>si</address-line-1>
<address-line-2></address-line-2>
<city>sid</city>
<state-code>IA</state-code>
<postal-code>88888</postal-code>
<postal-code-ext></postal-code-ext>
<country-code>USA</country-code>
</ship-to>
<total-points-spent>56</total-points-spent>
<date-time>2006-03-27 14:05:21.0</date-time>
<item-redeemed>
<item-number>042932-00</item-number>
<item-description>Pet Dinnerware: Large Stand </item-description>
<qty>1</qty>
<price>56.0</price>
<line-number>1</line-number>
</item-redeemed>
</redemption>
</customer-redemption>

 

[Anthony Jones]

I have an xml data that I want to store in a database.

I want the following fields
1. Account-id
2. order-number
3. first-name
4. last-name
5. address-line
6. address-line-2
7 city
8, state
9 postal-code
10 total-points-spent
11 the list of items that were purchased which are in the section
item-redeemed


And this is the asp code that I use to move through the list. right now all
I can get is the redemption top-level, but I cannot seem to go down to the
subitems. Can anyone help out here and help get this into a asp script.
Thanks.

set root = xmlDom.documentElement set orderheaders =
root.selectNodes("redemption") for counter=0 to orderheaders.length-1 for
each header in orderheaders.item(counter).childnodes 'set subheader =
header.selectnodes("item-redeemed") 'for counter1=0 to subheader.length-1
'for each itemhead in subheader.item(counter1).childnodes response.Write "
" & header.nodename & "=" & header.text select case header.nodename case
"total-points-spent" response.Write "Points spent = " & header.text & "
" end select 'next 'next next next set orderheaders=nothing set root =
nothing


<customer-redemption>
<partner-client>someid</partner-client>
<partner-client-participant-id>
<account-id>F4B62207-3</account-id>
<user-name>3</user-name>
<password>yeahyeah</password>
</partner-client-participant-id>
<redemption>
<order-number>213387</order-number>
<ship-to>
<first-name>J</first-name>
<last-name>D</last-name>
<address-line-1>si</address-line-1>
<address-line-2></address-line-2>
<city>sid</city>
<state-code>IA</state-code>
<postal-code>88888</postal-code>
<postal-code-ext></postal-code-ext>
<country-code>USA</country-code>
</ship-to>
<total-points-spent>56</total-points-spent>
<date-time>2006-03-27 14:05:21.0</date-time>
<item-redeemed>
<item-number>042932-00</item-number>
<item-description>Pet Dinnerware: Large Stand </item-description>
<qty>1</qty>
<price>56.0</price>
<line-number>1</line-number>
</item-redeemed>
</redemption>
</customer-redemption>

Use XPath. For example to retrieve the Account-id you could use:-

x = root.selectSingleNode("partner-client-participant-id/account-id").text

address line 1 is:-

x = root.selectSingleNode("redemption/ship-to/address-line-1").text

and so on.


However you didn't state which DB you are using. if using SQL Server you
could pass the whole XML to SQL Server and use OPENXML to perfom the
extraction.

Anthony.

 

[J.D.]

I am using sql server 2000 and how would you use openxml to do this?

 

[J.D.]

DECLARE @sXml varchar(8000)

SET @sXml = '<customer-redemption>
<partner-client>someid</partner-client>
<partner-client-participant-id>
<account-id>F4B62207-3</account-id>
<user-name>3</user-name>
<password>yeahyeah</password>
</partner-client-participant-id>
<redemption>
<order-number>213387</order-number>
<ship-to>
<first-name>J</first-name>
<last-name>D</last-name>
<address-line-1>si</address-line-1>
<address-line-2></address-line-2>
<city>sid</city>
<state-code>IA</state-code>
<postal-code>88888</postal-code>
<postal-code-ext></postal-code-ext>
<country-code>USA</country-code>
</ship-to>
<total-points-spent>56</total-points-spent>
<date-time>2006-03-27 14:05:21.0</date-time>
<item-redeemed>
<item-number>042932-00</item-number>
<item-description>Pet Dinnerware: Large Stand </item-description>
<qty>1</qty>
<price>56.0</price>
<line-number>1</line-number>
</item-redeemed>
<item-redeemed>
<item-number>042932-00</item-number>
<item-description>Pet Dinnerware: Large Stand </item-description>
<qty>1</qty>
<price>56.0</price>
<line-number>2</line-number>
</item-redeemed>

</redemption>
</customer-redemption>'

DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @sXML

SELECT xmltable.item_number FROM
OPENXML(@XMLDocPointer,'/customer-redemption/redemption/item-redeemed',2)
WITH (item_number varchar(50)) XMLTABLE

EXEC sp_xml_removedocument @XMLDocPointer
RETURN

I am getting a number value on the select statement on the xml, any idea why
that is so

 

[J.D.]

figured it out, thanks for the help

DECLARE @sXml varchar(8000)

SET @sXml = '<customer-redemption>
<partner-client>someid</partner-client>
<partner-client-participant-id>
<account-id>F4B62207-3</account-id>
<user-name>3</user-name>
<password>yeahyeah</password>
</partner-client-participant-id>
<redemption>
<order-number>213387</order-number>
<ship-to>
<first-name>J</first-name>
<last-name>D</last-name>
<address-line-1>si</address-line-1>
<address-line-2></address-line-2>
<city>sid</city>
<state-code>IA</state-code>
<postal-code>88888</postal-code>
<postal-code-ext></postal-code-ext>
<country-code>USA</country-code>
</ship-to>
<total-points-spent>56</total-points-spent>
<date-time>2006-03-27 14:05:21.0</date-time>
<item-redeemed>
<item-number>042932-00</item-number>
<item-description>Pet Dinnerware: Large Stand </item-description>
<qty>1</qty>
<price>56.0</price>
<line-number>1</line-number>
</item-redeemed>
<item-redeemed>
<item-number>042932-00</item-number>
<item-description>Pet Dinnerware: Large Stand </item-description>
<qty>1</qty>
<price>56.0</price>
<line-number>2</line-number>
</item-redeemed>

</redemption>
</customer-redemption>'

DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @sXML

SELECT xmltable.item_number FROM
OPENXML(@XMLDocPointer,'/customer-redemption/redemption/item-redeemed',2)
WITH (item_number varchar(50)) XMLTABLE

EXEC sp_xml_removedocument @XMLDocPointer
RETURN

I am getting a number value on the select statement on the xml, any idea
why that is so