Stored PRoc using OPENXML 1

[azrobert]

Hello,

I am working on a stored proc using Openxml
I have used this in the past successfully but have run
in to a snag with a new xml file.
I am reading the file from disk and passing it to the SP.

It appears that there are sub elements in teh XML file and I have never worked with those. I will include a sample of the xml file and the SP and try to explain the problem.

XML FILE

<?xml version="1.0" encoding="UTF-8"?>
<OrderGuideData>
<OrderGuideHeader>
<DistributorLocation>5146</DistributorLocation>
<DistributorName>PHOENIX</DistributorName>
<DistributorCustomerNumber>62598009</DistributorCustomerNumber>
<DistributorCustomerName>Store 1</DistributorCustomerName>
<DepartmentNumber>0</DepartmentNumber>
<OrderGuideNumber>4212516</OrderGuideNumber>
<EffectiveDate>
<Year>2010</Year>
<Month>11</Month>
<Day>1</Day>
</EffectiveDate>
</OrderGuideHeader>
<OrderGuideItem>
<OrderGuideLineNumber>108.0</OrderGuideLineNumber>
<DistributorProductNumber>8270555</DistributorProductNumber>
<CustomerProductNumber>3000</CustomerProductNumber>
<ProductDescription>LEMON, STD FRESH REF</ProductDescription>
<ProductLabel>PACKER</ProductLabel>
<SalesPackSize>140 EA</SalesPackSize>
<ManufacturerProductNumber/>
<ProductStatus/>
<SalesUOM>CS</SalesUOM>
<PriceUOM>CS</PriceUOM>
<GrossWeight>40.000</GrossWeight>
<NetWeight>38.000</NetWeight>
<CatchWeight>N</CatchWeight>
<WillBreak>false</WillBreak>
<EachPerCase>1 EA</EachPerCase>
<CasePrice>
<CurrencyAmount>
<Currency>USD</Currency>
<Amount>27.7500</Amount>
</CurrencyAmount>
</CasePrice>
<EachPrice>
<CurrencyAmount>
<Currency>USD</Currency>
<Amount>0.2200</Amount>
</CurrencyAmount>
</EachPrice>
</OrderGuideItem>
</OrderGuideData>


STORED PROCEDURE

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[VM_USFoodsUpdate]

(

@XmlData XML

)

AS

BEGIN

DECLARE @idoc int



--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlData



-- Update existing entries

UPDATE Ingredients

SET MenuUnitPrice = Xdoc.Amount


FROM OPENXML (@idoc, '/OrderGuideData/Table', 2)

WITH
(
DistributorProductNumber int,
WillBreak VarChar(25),
SellingPrice2 Money ) XDoc

WHERE Com_ProdLinks.IngNum = XDoc.IngNum
EXEC SP_XML_RemoveDocument @Idoc

END




I realsize that some of the fields do not match in the SP example.... As I was working on it I realzed that the <CasePrice Nodes seem to show up as a second table.....

I am sure there is something simple I am missing
I am really looking for the 2 amount columns for the case price and the each price.

Thanks in advance for any help!

 

[tsuji]

The openxml() part can look like this. I put some duplicate data in different column names, two amount (you probably like to have caseprice(?)) and also that IngNum otherwise what is Xdoc.Amount and Xdoc.IngNum?!
[tt]
UPDATE Ingredients
SET MenuUnitPrice = Xdoc.[blue]Amount[/blue]
FROM
openxml(@idoc,'/OrderGuideData/[red]OrderGuideItem[/red]',2)
with (
DistributorProductNumber int,
WillBreak VarChar(25),
-- SellingPrice2 Money
CasePrice_Amount money 'CasePrice/CurrencyAmount/Amount',
EachPrice_Amount money 'EachPrice/CurrencyAmount/Amount',
[blue]IngNum[/blue] int 'DistributorProductName',
[blue]Amount[/blue] money 'CasePrice/CurrencyAmount/Amount'
) XDoc
WHERE Com_ProdLinks.IngNum = XDoc.[blue]IngNum[/blue]
[/tt]

 

[azrobert]

Thank you very much......