January 24, 2003 at 7:46 am
I wonder if anyone can help. I am using XML passed to a stored procedure and am trying to extract the data. This has worked very well unitl I reached the sample show below. If you copy this into Query Analyzer and run it (it is self contained no tables required!) I am unable to make the Select statement display the indivual row lines from the description (DescLine d="1", "2" and "3" - all I can manage to do is to repeat line 1 for all three "lines". Can anyone spot what I am doing wrong here?
I have iontrodcued three select statements in an attmept to better analyse the data.
Any help appreciated.
Alan
=========================================
Declare @idoc int
Declare @doc varchar(8000)
Set @doc = '<?xml version="1.0"?>
<MiscInfoResponse><Status>OK</Status>
<Account>BOOKTHAT</Account>
<PropertyDetail p="1"><PropertyID>5685</PropertyID><Name>Ardnockeen, nr. Killorglin</Name><TypeDesc>Detached bungalow for 10</TypeDesc><SalesLine>Lovely views</SalesLine><Description><DescLine d="1">Centrally located for touring the whole of Kerry, this spacious bungalow lies in a quiet backwater 2 ½ miles from the town of Killorglin with its numerous shops and pubs.</DescLine><DescLine d="2">Surrounded by the owner’s farmland you are welcome to walk the fields to the nearby river (check where the bull is first!) or just sit back and relax in the sun lounge.</DescLine><DescLine d="3">The long sandy beach at Rossbeigh is 10 miles, picturesque Caragh Lake is 3 miles and there is easy access for exploring the Ring of Kerry and also the Dingle Peninsula.</DescLine></Description><Facilities>Shop 1 ½ miles, pub 2 ½ miles.</Facilities><Accommodation>Lounge/kitchen/diner, second lounge, sun lounge, bathroom with shower over bath, toilet and basin, separate toilet and basin; 5 bedrooms: 1 double with en-suite shower, toilet and basin, 3 double, 1 with wash basin, 1 twin. Separate shower room in hallway on £1 meter.</Accommodation><Services>Oil central heating, open fire in lounge, all fuel and electricity included in rent, bed linen and towels included in rent, cot, highchair, colour TV, video, stereo hi-fi, washing machine, tumble dryer, dishwasher, microwave; lawned garden, garden furniture; off road parking for 4 cars. Pets welcome.</Services><CustomerComments>
</CustomerComments><TPD>Yes</TPD></PropertyDetail></MiscInfoResponse>
'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
Select * from OPENXML(@idoc,'/MiscInfoResponse/PropertyDetail',2)
WITH (
--CountryID int '../../../CountryID',
--AreaGroupID int '../../AreaGroupID',
--AreaID int '../AreaID',
PropertyID int,
Name varchar (50),
TypeDesc varchar(50),
SalesLine varchar(2000),
Facilities varchar(2000),
Services varchar(2000),
CustomerComments varchar(2000)
)
Select * from OPENXML(@idoc,'/MiscInfoResponse/PropertyDetail')
--WITH (
--CountryID int '../../../CountryID',
--AreaGroupID int '../../AreaGroupID',
--AreaID int '../AreaID',
--PropertyID int '../../PropertyID',
--LineNum int '@d',
--DescLine varchar(2000) '../Description'
--)
Select * from OPENXML(@idoc,'/MiscInfoResponse/PropertyDetail/Description/DescLine')
WITH (
--CountryID int '../../../CountryID',
--AreaGroupID int '../../AreaGroupID',
--AreaID int '../AreaID',
PropertyID int '../../PropertyID',
LineNum int '@d',
Description varchar(2000) '../../Description'
)
--FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
-- WITH (OrderID int '../@OrderID',
-- CustomerID varchar(10) '../@CustomerID',
-- OrderDate datetime '../@OrderDate',
-- ProdID int '@ProductID',
-- Qty int '@Quantity')
EXEC sp_xml_removedocument @idoc
January 24, 2003 at 9:21 am
Try using the XPath test function. This seems to work:
Select * from OPENXML(@idoc,'/MiscInfoResponse/PropertyDetail/Description/DescLine',3)
WITH (LineNumber int '@d' ,
Description varchar(8000) 'text()'
)
HTH - Nigel
January 24, 2003 at 9:35 am
Thank you very much - this seems to be fine - well done!!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply