help needed on XML

using the BOL and looking through some of the posts on here ive managed to
come up with the following code in T-SQl
DECLARE @.TESTXML varchar(8000)
SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
<BDocument>,
<Data>
<InputReport>
<Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
reportSequenceNumber="000121" userNumber="123456">
<ProducedOn time="19:21:22" date="2004-09-30"/>
<ProcessingDate date="2004-10-01"/>
</Header>
</InputReport>
</Data>
</BDocument>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
SELECT *
FROM
OPENXML(@.hDoc, '/BDocument')
EXEC sp_xml_removedocument @.hDoc
I need help on expanding this.
1. Would i be right in assuming that to load the contants of a XML file
into the variable @.TESTXML, i would need to use something like actixex in a
DTS.
2. how can i in this case just do a select on a specific field ie
'reportnumber'
3. some of the reports i will be recieving will have the same field names in
different sections, for example
- <AccountTotals>
- <DebitEntry>
<AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
</DebitEntry>
</AccountTotal>
- <CreditEntry>
<AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
<UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
<AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
</CreditEntry>
As you can see the field 'numberOf' is used several times, how can i
differanciate between each one in each section
See below.
Best regards
Michael
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3A396C18-0271-4DD4-B2B5-C74505CE1156@.microsoft.com...
> using the BOL and looking through some of the posts on here ive managed to
> come up with the following code in T-SQl
> DECLARE @.TESTXML varchar(8000)
> SET @.TESTXML = '<?xml version="1.0" encoding="ISO-8859-1"?>
> <BDocument>,
> <Data>
> <InputReport>
> <Header reportType="REFT2013" reportNumber="999999" batchNumber="026"
> reportSequenceNumber="000121" userNumber="123456">
> <ProducedOn time="19:21:22" date="2004-09-30"/>
> <ProcessingDate date="2004-10-01"/>
> </Header>
> </InputReport>
> </Data>
> </BDocument>'
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> SELECT *
> FROM
> OPENXML(@.hDoc, '/BDocument')
> EXEC sp_xml_removedocument @.hDoc
> I need help on expanding this.
> 1. Would i be right in assuming that to load the contants of a XML file
> into the variable @.TESTXML, i would need to use something like actixex in
> a
> DTS.
Not necessarily. Any client side API that allows you to pass a parameter to
a stored proc should work. Just copy the file content over as parameter
value.

> 2. how can i in this case just do a select on a specific field ie
> 'reportnumber'
The OpenXML above results in an edge table.
To get the reportNumber for every header, you would replace your select
with:
select *
from OpenXML(@.hDoc, '/BDocument/Data/InputReport/Header') WITH (rno int
'@.reportNumber')

> 3. some of the reports i will be recieving will have the same field names
> in
> different sections, for example
> - <AccountTotals>
> - <DebitEntry>
> <AcceptedRecords numberOf="1" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <TotalsRecords numberOf="1" valueOf="0.00" currency="GBP" />
> </DebitEntry>
> </AccountTotal>
> - <CreditEntry>
> <AcceptedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <RejectedRecords numberOf="0" valueOf="0.00" currency="GBP" />
> <UserTrailerTotals numberOf="0" valueOf="0.00" currency="GBP" />
> <AdjustmentRecords numberOf="0" valueOf="0.00" currency="GBP" />
> </CreditEntry>
>
> As you can see the field 'numberOf' is used several times, how can i
> differanciate between each one in each section
The following will give you only AcceptedRecords:
select * from OpenXML(@.hDoc, '//AcceptedRecords') with
(numberOf int, valueOf real, currency nvarchar(5))
The following will give you numberOf and the name of its element:
select * from OpenXML(@.hDoc, '//DebitEntry/*') with
(recname nvarchar(40) '@.mp:localname', numberOf int)
HTH
Michael

help needed for 2 tables

hi everyone, i've added 2 tables one is on adding, the other one is on returning. but when i return, the dropdownlist keeps displaying those which are returned already. it shouldnt display because i've already returned.

here are the codes that i add,

"INSERT INTO record VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + toName + "', '" + toRemarks + "', '" + purpose + "', '" + timeEntered + "', '" + dateEntered + "', 'Out')"

here are the codes that i return

"INSERT INTO returnRecord VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + timeEntered + "', 'In')"

here are the codes that i wanna display in the dropdownlist which i have set the status = IN. means they have returned already so it should display.

this is wad i tried what its wrong -->

"select a.manifestNo from record a, returnRecord r where a.status = 'Out' And r.status = 'In'"

my dropdownlist cant retrieve those records that are already returned. i suspect is the select statement that is wrong. but i cant figure out how. please help me!

regards, ethan

Instead of using inline SQL you really should use stored procedures, if you rewrite these as stored procedures you will be able to test them more effectively in sql management studio. Are you trying to sleect manifestNo from both tables, i think the error lies around returnecord r - you are specifiying the table but the select.

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?
master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

Help me get MSDE SP3a installed on Win Server 2003

I've tried every which way I can from the docs and but I can't seem to get MSDE to install correctly. Found out that Win2003 only runs SP3a, downloaded and tried it.
tried these different commands from the prompt:
setup SAPWD="xxxxx" INSTANCENAME="xxxxx" TARGETDIR="xxxx" (from MSDE2000 only)
setup /upgradesp sqlrun INSTANCENAME="xxxxx" DISABLENETWORKPROTOCOLS=1 (after installing MSDE2000 first)
setup INSTANCENAME="xxxxx" SAPWD="xxxxx" (straight from MSDE2000 SP3a)
Services shows that the SQL agent and the MSSQL$xxxx running. MSSQLServerADHelper is not running. The Service Manager isn't showing any services in the tray nor does it have the little green arrow. I can't connect to anything with Enterprise Manager eithe
r.
What do I need to do? Please note, I'm a totally complete noob with SQL admin and set up...
Thanks...
PS: and note that I'm in the correct directory with the installer and the xxxx are the correct instances and pw's
addition: This is Win 2003 Server Standard Edition...