davidsalazar01 (2/12/2013)
I'm getting the following error when executing the following SQL:SELECT ParamValues.XMLProfile_TXT.value('.','VARCHAR(MAX)') AS DID
FROM Payment_Method_T (nolock)
OUTER APPLY XMLProfile_TXT.nodes('/div/ul/li/did') ParamValues(XMLProfile_TXT);
Msg 9506, Level 16, State 1, Line 1
The XMLDT method 'nodes' can only be invoked on columns of type xml.
Can you let me know what I'm doing wrong?
I'm going to take a wild guess and say that you aren't storing it as XML, which is why I asked for DDL scripts in my first post: -
Cadavre (2/12/2013)
If you could knock up some sample data and DDL scripts, then your expected results based on the sample data provided that would be extremely useful in allowing people to help you.Have a read through this link --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, if you're unsure how best to lay this information out. Remember that if your sample data and DDL script is readily consumable, then the volunteers for this forum are much more likely to take time out from their work to give you a hand.
With that in mind, you could cast it to XML, but since I can no longer be certain that your stored data is correct XML syntax it may throw up errors so will probably be easier to use the splitter as suggested by Lowell.
davidsalazar01 (2/12/2013)
Also, is this whole string required (e.g. '/div/ul/li/did')? I only want /DID tag only. What is the other chars for?
Those other characters are required to show where the "did" tag is in relation to the XML schema. I based that on the sample data provided by Lowell because you didn't provide anything for me to work with.