Read Value in XML Node - T-SQL

Question

This is my code .......

DECLARE @XML AS XML; SET @XML = CAST('<Session id="ID969138672" realTimeID="4300815712"> <VarValues> <varValue id="ID123" source="Internal" name="DisconnectedBy">VisitorClosedWindow</varValue> <varValue id="ID1234" source="PreChat" name="email"> [e-mail] </varValue> </VarValues> </Session> ' AS XML) SELECT xmlData.Col.value('@id','varchar(max)') ,xmlData.Col.value('@source','varchar(max)') ,xmlData.Col.value('@name','varchar(max)') FROM @XML.nodes('//Session/VarValues/varValue') xmlData(Col); 

This is the result .....

How to include actual varValue values?

I need to read the values ​​of VisistorClosedWindow and [e-mail] as well

Answer 1

You can get this by following these steps:

 xmlData.Col.value('.','varchar(max)') 

Thus, the choice will be:

 SELECT xmlData.Col.value('@id','varchar(max)') ,xmlData.Col.value('@source','varchar(max)') ,xmlData.Col.value('@name','varchar(max)') ,xmlData.Col.value('.','varchar(max)') FROM @XML.nodes('//Session/VarValues/varValue') xmlData(Col); 

Answer 2

Just use the line .value('.', 'varchar(50) ) for this:

 SELECT xmlData.Col.value('@id','varchar(25)'), xmlData.Col.value('@source','varchar(50)'), xmlData.Col.value('@name','varchar(50)'), xmlData.Col.value('.','varchar(50)') -- <== this gets your the element value FROM @XML.nodes('//Session/VarValues/varValue') xmlData(Col);