SQL Server Performance Forum – Threads Archive

XML question – how now?

Hi all – it’s me (again! [:I]). I’m trying to get my head around handling XML data, and specifically trying to find elements, and enchance XML received. At a high-level description, I need to handle an XML file, join to a SQL resultset, and then insert an element into the XML, and write/update the source XML file. here’s a brief sample of the XML data: ’<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE RECORDS SYSTEM "records.dtd"> <RECORDS> <RECORD> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>34699</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="Hierarchy"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>DVDs</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_NewBadger"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>New Value 1</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="New Navigable Dimension"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>TEST1</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>34699</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Year"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>1992</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Wine"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>A Red Blend Alexander Valley</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Winery"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Lyeth</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_PriceStr"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>18.00</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Score"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>5</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Region"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_DateReviewed"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>08/31/95</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType1"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Red</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Year2"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>1992</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType2"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cabernet Blend</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_URLString"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>LYETH Cabernet Blend Alexander Valley A Red Blend 1992 Cabernet Blend Red</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Description"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Supple and polished cedar, coffee, cherry and berry flavors. This is elegant, finishing with firm tannins and good length. Drinkable now.</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Berry</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cedar</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cherry</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Coffee</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Elegant</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Firm</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Firm Tannins</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL&gt<img src=’/communi ty/emoticons/emotion-4.gif’ alt=’;P’ />olished</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Supple</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Tannins</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Appellations"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Alexander Valley</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Appellations"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Name"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>A Red Blend Alexander Valley</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Price"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>18.00</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Red</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cabernet Blend</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Berry</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cedar</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cherry</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Coffee</PVAL> </PROP> </RECORD> <RECORD> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>34700</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="Hierarchy"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>DVDs</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_NewBadger"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>New Value 2</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="New Navigable Dimension"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>TEST1</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>34700</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Year"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>1992</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Wine"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>A Tribute White Sonoma Mountain</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Winery"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Benziger</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_PriceStr"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>16.00</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Score"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>15</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Region"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_DateReviewed"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>08/31/95</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType1"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>White</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Year2"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>1992</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType2"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sauvignon Blend</PVAL> </PROP& gt; &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_URLString"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>BENZIGER Sauvignon Blend Sonoma Mountain A Tribute 1992 Sauvignon Blend White</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Description"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Crisp in texture, nicely harmonious in the way in weaves its spicy, toasty-onion nuances through the fig and lemon fruit. Ready now. (200 cases produced)</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Fig</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Fruit</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Lemon</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Toasty</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Crisp</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Harmonious</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Appellations"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Appellations"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma Mountain</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Name"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>A Tribute White Sonoma Mountain</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Price"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>16.00</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>White</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sauvignon Blend</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Fig</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Fruit</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Lemon</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Toasty</PVAL> </PROP> </RECORD> </RECORDS>' I need to find the &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID">, and use the related PVAL – for example 34699 and 34700 to join with a resultset that will look something like: 34699,’Available' 34700,’UnAvailable' Then I need to insert a new element into the <RECORD> node, being: &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Availability"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>"Available"</PVAL> </PROP> I’m grappling with OPENXML or XQUERY etc., to find a way to query the values. the BOL sample: DECLARE @x xml SET @x = ‘<ROOT><a>111</a></ROOT>' SELECT @x.query(‘/ROOT/a’) returns: 111 That seems to be what I would need to do, but I have issues with: I declare @X xml, and set it = sample XML above. SELECT @x.query(‘/RECORDS/RECORD/PROP NAME="P_WineID"/pval’) returns ERROR: Msg 2209, Level 16, State 1, Line 221 XQuery [query()]: Syntax error near ‘PROP' SELECT @x.query(‘/RECORDS/RECORD/P_WineID/pval’) returns what seems null or an emtpy string: XML DTD has been stripped from one or more XML fragments. External subsets, if any, have been ignored. ———————————————…(truncated this line) (1 row(s) affected) SELECT @x.query(‘/RECORDS/RECORD/PROP/pval’) returns same as SELECT @x.query(‘/RECORDS/RECORD/P_WineID/pval’) SELECT @x.query(‘/RECORDS/RECORD/PROP="P_WineId"/pval’) returns ERROR Msg 2374, Level 16, State 1, Line 221 XQuery [query()]: A node or set of nodes is required for / …. Am I beyond help here? How do I identify the element &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> so I can get the PVAL ? Panic, Chaos, Disorder … my work here is done –unknown

Have you checked the web for any reference, may refer tohttp://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part9/c3161.mspx for any help. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Some progress made, but I still won’t claim to understand it yet. I’m a little worried that, because of the structure of the XML I’m getting, it is going to be difficult to get handle it – if yoy notice the XML I gave above, there are duplicate sets, like : <pre id="code"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rop Name="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>36799</pval> </prop> </pre id="code"> which appears twice in the 1st <RECORD> node. I’m hoping to tryt and find out if thsat is a mistake in the XML sample I was given. In the meantime, the code I have is: <pre id="code"> set nocount on DECLARE @idocint, @TPNBint, @docvarchar(8000) DECLARE @XMLDocumentTable TABLE (AttNAmevarchar(50), PVALvarchar(500)) SET @doc =’<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE RECORDS SYSTEM "records.dtd"> <RECORDS> <RECORD> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>34699</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="Hierarchy"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>DVDs</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_NewBadger"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>New Value 1</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="New Navigable Dimension"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>TEST1</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>34699</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Year"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>1992</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Wine"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>A Red Blend Alexander Valley</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Winery"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Lyeth</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_PriceStr"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>18.00</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Score"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>5</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Region"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_DateReviewed"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>08/31/95</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType1"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Red</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Year2"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>1992</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType2"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cabernet Blend</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_URLString"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>LYETH Cabernet Blend Alexander Valley A Red Blend 1992 Cabernet Blend Red</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Description"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Supple and polished cedar, coffee, cherry and berry flavors. This is elegant, finishing with firm tannins and good length. Drinkable now.</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Berry</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cedar</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cherry</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Coffee</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/communit y/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Elegant</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Firm</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Firm Tannins</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />olished</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Supple</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Tannins</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Appellations"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Alexander Valley</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Appellations"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Name"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>A Red Blend Alexander Valley</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Price"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>18.00</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Red</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cabernet Blend</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Berry</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cedar</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Cherry</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Coffee</PVAL> </PROP> </RECORD> <RECORD> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>34700</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="Hierarchy"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>DVDs</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_NewBadger"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>New Value 2</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="New Navigable Dimension"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>TEST1</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineID"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>34700</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Year"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>1992</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Wine"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>A Tribute White Sonoma Mountain</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Winery"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Benziger</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_PriceStr"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>16.00</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Score"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>15</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Region"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_DateReviewed"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>08/31 /95</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType1"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>White</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Year2"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>1992</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType2"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sauvignon Blend</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_URLString"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>BENZIGER Sauvignon Blend Sonoma Mountain A Tribute 1992 Sauvignon Blend White</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Description"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Crisp in texture, nicely harmonious in the way in weaves its spicy, toasty-onion nuances through the fig and lemon fruit. Ready now. (200 cases produced)</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Fig</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Fruit</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Lemon</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavors"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Toasty</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Crisp</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Body"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Harmonious</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Appellations"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Appellations"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sonoma Mountain</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Name"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>A Tribute White Sonoma Mountain</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Price"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>16.00</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>White</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_WineType"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Sauvignon Blend</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Fig</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Fruit</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Lemon</PVAL> </PROP> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ROP NAME="P_Flavor"> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />VAL>Toasty</PVAL> </PROP> </RECORD> </RECORDS>' –Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc – Execute a SELECT statement that uses the OPENXML rowset provider. INSERT @XMLDocumentTable (AttNAme, PVAL) SELECT [email protected] = PVAL [Name], PVAL –* FROM OPENXML (@idoc, ‘/RECORDS/RECORD/PROP’,3) WITH ( [NAME]varchar(50)’@Name’, PVALvarchar(500)’./PVAL’) SELECT AttNAme,PVAL FROM @XMLDocumentTable </pre id="code"> This gives me the RESULTS <pre id="code"> AttNAme PVAL ————————————————– ————————————————— NULL 34699 NULL DVDs NULL New Value 1 NULL TEST1 NULL 34699 NULL 1992 NULL A Red Blend Alexander Valley NULL Lyeth NULL 18.00 NULL 5 NULL Sonoma NULL 08/31/95 NULL Red NULL 1992 NULL Cabernet Blend NULL LYETH Cabernet Blend Alexander Valley A Red Blend 1992 Cabernet Blend Red NULL Supple and polished cedar, coffee, cherry and berry flavors. This is elegant, finishing with firm tannins and good length. Drinkable now. NULL Berry NULL Cedar NULL Cherry NULL Coffee NULL Elegant NULL Firm NULL Firm Tannins NULL Polished NULL Supple NULL Tannins NULL Alexander Valley NULL Sonoma NULL A Red Blend Alexander Valley NULL 18.00 NULL Red NULL Cabernet Blend NULL Berry NULL Cedar NULL Cherry NULL Coffee NULL 34700 NULL DVDs NULL New Value 2 NULL TEST1 NULL 34700 NULL 1992 NULL A Tribute White Sonoma Mountain NULL Benziger NULL 16.00 NULL 15 NULL Sonoma NULL 08/31/95 NULL White NULL 1992 NULL Sauvignon Blend NULL BENZIGER Sauvignon Blend Sonoma Mountain A Tribute 1992 Sauvignon Blend White NULL Crisp in texture, nicely harmonious in the way in weaves its spicy, toasty-onion nuances through the fig and lemon fruit. Ready now. (200 cases produced) NULL Fig NULL Fruit NULL Lemon NULL Toasty NULL Crisp NULL Harmonious NULL Sonoma NULL Sonoma Mountain NULL A Tribute White Sonoma Mountain NULL 16.00 NULL White NULL Sauvignon Blend NULL Fig NULL Fruit NULL Lemon NULL Toasty </pre id="code"> I just need to try and figure out how to put the NAME values into the AttName… Any ideas? Panic, Chaos, Disorder … my work here is done –unknown

Â£$%!Â£$%!Â£^$ so – XML is case sensitive. I changed @Name to @NAME, and now it works…. *sighs* the walls around here all seem to have a dent that is consistent with my shape of my forehead Panic, Chaos, Disorder … my work here is done –unknown

]]>