Read value in XML Node - T-SQL
27,381
Solution 1
You can get that by doing this:
xmlData.Col.value('.','varchar(max)')
So the select would 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);
Solution 2
Just use the .value('.', 'varchar(50)
) line for that:
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's value
FROM @XML.nodes('//Session/VarValues/varValue') xmlData(Col);
Author by
Etienne
Updated on February 12, 2020Comments
-
Etienne over 4 years
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">[email protected]</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 output.....
How can I include the actual values of the varValue?
I need to read the values VisistorClosedWindow and [email protected] values as well