Select for XMLDATA content value

Apr 27, 2010 at 1:18 PM

Hi I am trying to use the XMLDATA value for products, and it works perfectly. 
Problem is I never used XML data in my own queries and select sentences  

in database table "NB_Store_ProductLang" it looks something like this

<genxml>
     <textbox>
        <kategorifarver>10</kategorifarver>
        <vejlpris>44</vejlpris>      
     </textbox>  
<checkbox />  
<dropdownlist />  
<checkboxlist />  
<radiobuttonlist />
</genxml>

I am wondering is there some kind of  SQL SELECT that I could use to get these values in a VIEW or adding the columns to table in a select 

Apr 27, 2010 at 1:29 PM

Hi,

Yes, but I've not got an example for NB_Store, however here is an example view from another system I have..

This is not what you want, but you should be able to get the idea of what you need to do..

Regards,

Dave.

(P.S.  If you do get it working , please think about posting it back here so other people can use it, thanks)

-------------------------------------------------------------------------------------------

SELECT     ItemID AS DBItemID, TypeCode AS DBTypeCode, XMLData.value('(genxml/hidden/taskref)[1]', 'nvarchar(256)') AS TaskRef,
                      XMLData.value('(genxml/hidden/createdby)[1]', 'nvarchar(256)') AS CreatedBy, XMLData.value('(genxml/hidden/clientid)[1]', 'nvarchar(256)') AS ClientID,
                      XMLData.value('(genxml/hidden/itemid)[1]', 'nvarchar(256)') AS ItemID, XMLData.value('(genxml/hidden/dtemodifieddate)[1]', 'nvarchar(256)')
                      AS dteModifiedDate, XMLData.value('(genxml/hidden/lockedby)[1]', 'nvarchar(256)') AS LockedBy,
                      XMLData.value('(genxml/hidden/htmldisplaylockedby)[1]', 'nvarchar(256)') AS htmlDisplayLockedBy, XMLData.value('(genxml/textbox/txtclientref)[1]',
                      'nvarchar(256)') AS txtClientRef, XMLData.value('(genxml/textbox/txttaskname)[1]', 'nvarchar(256)') AS txtTaskName,
                      XMLData.value('(genxml/textbox/txtusername)[1]', 'nvarchar(256)') AS txtUserName, XMLData.value('(genxml/textbox/dtereminder)[1]', 'nvarchar(256)')
                      AS dteReminder, XMLData.value('(genxml/textbox/txtclientname)[1]', 'nvarchar(256)') AS txtClientName,
                      XMLData.value('(genxml/checkbox/chkcompleted)[1]', 'bit') AS chkCompleted, XMLData.value('(genxml/textbox/txtcompanyname)[1]', 'nvarchar(256)')
                      AS txtCompanyName, XMLData.value('(genxml/radiobuttonlist/rblowner)[1]', 'nvarchar(256)') AS rblOwner,
                      XMLData.value('(genxml/textbox/txtpersonalemail)[1]', 'nvarchar(256)') AS txtPersonalEmail, XMLData.value('(genxml/textbox/txttaskdesc)[1]',
                      'nvarchar(256)') AS txtTaskDesc, XMLData.value('(genxml/textbox/txtlog)[1]', 'nvarchar(256)') AS txtLog, XMLData.value('(genxml/textbox/auditlog)[1]',
                      'nvarchar(256)') AS auditLog
FROM         dbo.NEvoWeb_EspaceXML
WHERE     (TypeCode = 'TASKS')

 

 

Apr 27, 2010 at 2:19 PM

Thank you so much David. 

As always this worked perfectly..    I rand this code and got a nice standard table as reply with the custom XML data as separate columns

SELECT     ProductID, ProductName,
XMLData.value('(genxml/textbox/kategorifarver)[1]', 'nvarchar(256)') AS kategorifarver,  
XMLData.value('(genxml/textbox/vejlpris)[1]', 'nvarchar(256)') AS vejlpris
FROM         dbo.NB_Store_ProductLang

Of course we could have selected other values from  XML  but this is all we needed for this q uery  

Fantiastic.