I fail to put a XML into a table

[geir]

Hi all.
I just can't get to the item values and hope anyone can help me. Here is the
XML i have got into at one row tabel an XML datatype column.

CREATE TABLE #LevInfoXML (pk int primary key identity, XmlCol xml)

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<ItemStocks>
<Item>
<ThiemeItemCode>0100159</ThiemeItemCode>
<LargeQuantity>0</LargeQuantity>
<FulfillmentConditions>K3</FulfillmentConditions>
<FulfillmentTime>3 Days</FulfillmentTime>
<Title>@</Title>
<TitleML/>
<AvailabilityStatus/>
<DropShipment>Yes</DropShipment>
<GrossWeight>0,01</GrossWeight>
<PackageSize-Length>0,01</PackageSize-Length>
<PackageSize-Width>0,01</PackageSize-Width>
<PackageSize-Height>0,01</PackageSize-Height>
<EAN-Code/>
<AvailableStock>0</AvailableStock>
</Item>
<Item>
<ThiemeItemCode>0100351</ThiemeItemCode>
<LargeQuantity>0</LargeQuantity>
<FulfillmentConditions>K3</FulfillmentConditions>
<FulfillmentTime>3 Days</FulfillmentTime>
<Title>@</Title>
<TitleML/>
<AvailabilityStatus/>
<DropShipment>Yes</DropShipment>
<GrossWeight>1</GrossWeight>
<PackageSize-Length>0,1</PackageSize-Length>
<PackageSize-Width>0,1</PackageSize-Width>
<PackageSize-Height>0,1</PackageSize-Height>
<EAN-Code/>
<AvailableStock>0</AvailableStock>
</Item>
</ItemStocks>

The following 2 examples just give me NULL as values.

SELECT ThiemeItemCode = R.ref.value('@ThiemeItemCode', 'nvarchar(64)')
FROM #LevInfoXML cross apply xmlcol.nodes('//Item') as R(ref)

AND

SELECT ItemStocks.Item.value('@ThiemeItemCode', 'varchar(20)')
FROM
@XML.nodes('/ItemStocks/Item') AS ItemStocks(Item)

This should be really simple, but I just cant get to the items values.

--
Thanks all
Regards Geir

[Martin Honnen]

With XQuery/XPath
@ThiemeItemCode
is short for
attribute::ThiemeItemCode
but your 'Item' elements do not have any attributes at all. Instead they
have child elements so you want
child::ThiemeItemCode
or simply
ThiemeItemCode
I think to make the Microsoft SQL Server XQuery implementation of the
'value' function happy you need to add '[1]'
e.g.
R.ref.value('ThiemeItemCode[1]', 'nvarchar(64)')

--

Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

[Bob]

Try this:

SELECT
ThiemeItemCode = R.ref.value('ThiemeItemCode[1]', 'nvarchar(64)')
FROM #LevInfoXML cross apply xmlcol.nodes('/ItemStocks/Item') as R(ref)

HTH
wBob