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