SQL Server Performance

XML question - how now?

Discussion in 'SQL Server 2005 General Developer Questions' started by SQL_Guess, Jun 9, 2006.

  1. SQL_Guess New Member

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

  3. SQL_Guess New Member

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

    £$%!£$%!£^$

    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

Share This Page