How can I put this XML file into SQL Server...

[AProgrammer]

Hi,

I have the following XML file:

<Message>

<MessageInfo MID="ODX95”></MessageInfo>
<Module>
<Invoice number="43562" inv_date="000000">
<Data>
<Name csg="ABC1"></Name>
<PartNumber code="123" quantity="96"></PartNumber>
<Price unit_price="13309.00" total_amount="1277664.00"></Price>
<PartNumber code="345" quantity="24"></PartNumber>
<Price unit_price="20213.00" total_amount="485112.00"></Price>
<PartNumber code="678" quantity="120"></PartNumber>
<Price unit_price="16593.00" total_amount="1991160.00"></Price>
<VAT amount="3753936.00" vat_rate="0.000"></VAT>
<Total total_amount="3753936.00" vat_amount="3753936.00"></Total>
</Data>
</Invoice>
</Module>

</Message>

What I want is that I want to put this data into SQL Table.

exec sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO #Invoices (DocumentNumber, InvoiceNumber, InvoiceDate, PartNumber, Quantity, Price)
SELECT * FROM OPENXML (@idoc, '/Message/Module/Invoice/Data/PartNumber', 1)
WITH (
DocumentNumber varchar(30) '../../../../MessageInfo/@MID',
InvoiceNumber varchar(30) '../../@number',
InvoiceDate varchar(30) '../../@invoice_date',
PartNumber varchar(30) '@code',
Quantity varchar(30) '@quantity',
Price varchar(30) '../Price/@unit_price'
)
EXEC sp_xml_removedocument @idoc


The result I get is the following...

DocumentNumber InvoiceNumber InvoiceDate PartNumber Quantity Price
ODX95 43562 000000 123 96 13309.00
ODX95 43562 000000 345 24 13309.00
ODX95 43562 000000 678 120 13309.00

I am getting always the same Price value. Why is that? And how can I avoid this???

Any ideas???

Thanks....

[azimmer]

Hi,

I have the following XML file:

<Message>

<MessageInfo MID="ODX95”></MessageInfo>
<Module>
<Invoice number="43562" inv_date="000000">
<Data>
<Name csg="ABC1"></Name>
<PartNumber code="123" quantity="96"></PartNumber>
<Price unit_price="13309.00" total_amount="1277664.00"></Price>
<PartNumber code="345" quantity="24"></PartNumber>
<Price unit_price="20213.00" total_amount="485112.00"></Price>
<PartNumber code="678" quantity="120"></PartNumber>
<Price unit_price="16593.00" total_amount="1991160.00"></Price>
<VAT amount="3753936.00" vat_rate="0.000"></VAT>
<Total total_amount="3753936.00" vat_amount="3753936.00"></Total>
</Data>
</Invoice>
</Module>

</Message>

What I want is that I want to put this data into SQL Table.

exec sp_xml_preparedocument @idoc OUTPUT, @doc
INSERT INTO #Invoices (DocumentNumber, InvoiceNumber, InvoiceDate, PartNumber, Quantity, Price)
SELECT * FROM OPENXML (@idoc, '/Message/Module/Invoice/Data/PartNumber', 1)
WITH (
DocumentNumber varchar(30) '../../../../MessageInfo/@MID',
InvoiceNumber varchar(30) '../../@number',
InvoiceDate varchar(30) '../../@invoice_date',
PartNumber varchar(30) '@code',
Quantity varchar(30) '@quantity',
Price varchar(30) '../Price/@unit_price'
)
EXEC sp_xml_removedocument @idoc


The result I get is the following...

DocumentNumber InvoiceNumber InvoiceDate PartNumber Quantity Price
ODX95 43562 000000 123 96 13309.00
ODX95 43562 000000 345 24 13309.00
ODX95 43562 000000 678 120 13309.00

I am getting always the same Price value. Why is that? And how can I avoid this???

Any ideas???

Thanks....

Hi, I guess you need two modifications:
1. Data set: I belive that the closing tag for <PartNumber> is misplaced, move them to behind each </Price> tags like this:

<PartNumber code="123" quantity="96">
<Price unit_price="13309.00" total_amount="1277664.00"></Price></PartNumber>
<PartNumber code="345" quantity="24">
<Price unit_price="20213.00" total_amount="485112.00"></Price></PartNumber>
<PartNumber code="678" quantity="120">
<Price unit_price="16593.00" total_amount="1991160.00"></Price></PartNumber>
 

2. change the SELECT to:

SELECT  * FROM OPENXML (@idoc, '/Message/Module/Invoice/Data/PartNumber', 1) 
WITH (
DocumentNumber varchar(30) '../../../../MessageInfo/@MID',
InvoiceNumber varchar(30) '../../@number',
InvoiceDate varchar(30) '../../@invoice_date', 
PartNumber varchar(30) '@code',
Quantity varchar(30) '@quantity', 
Price varchar(30) 'Price/@unit_price'
)
 

(i.e. remove the '../' string from the 'Price' field).