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

**azimmer** · Oct 5 '07, 12:55 PM

Originally posted by AProgrammer

Hi,

I have the following XML file:

<Message>

<MessageInfo MID="ODX95”></MessageInfo>
<Module>
<Invoice number="43562" inv_date="00000 0">
<Data>
<Name csg="ABC1"></Name>
<PartNumber code="123" quantity="96"></PartNumber>
<Price unit_price="133 09.00" total_amount="1 277664.00"></Price>
<PartNumber code="345" quantity="24"></PartNumber>
<Price unit_price="202 13.00" total_amount="4 85112.00"></Price>
<PartNumber code="678" quantity="120"> </PartNumber>
<Price unit_price="165 93.00" total_amount="1 991160.00"></Price>
<VAT amount="3753936 .00" vat_rate="0.000 "></VAT>
<Total total_amount="3 753936.00" vat_amount="375 3936.00"></Total>
</Data>
</Invoice>
</Module>

</Message>

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

exec sp_xml_prepared ocument @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_removedo cument @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:

Code:

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

2. change the SELECT to:

Code:

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) '[B]Price[/B]/@unit_price'
)

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

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

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

Comment