Select XML Node Values from XML Field

Posted: September 8, 2010 in SQL Server 2005 - XML

To get values from XML nodes in an XML data type field, you can do the following:

-- Declare variables
DECLARE @lxml_value XML;

-- Create table with XML colum
CREATE TABLE tmp_xml (xml_col XML);

-- Set XML variables
SET @lxml_value = '<Transaction>
                      <Node1>Value 1</Node1>
                      <Node2>Value 2</Node2>
                      <Node3>Value 3</Node3>
                      <Node4>Value 4</Node4>
                      <Node5>Value 5</Node5>
                   </Transaction>';

-- Insert XML file into temp table
INSERT INTO tmp_xml 
           (xml_col)
VALUES     (@lxml_value);

-- Select the results from XML nodes
SELECT tt.xml_col.value('(/Transaction/Node1) [1]', 'VARCHAR(10)') xml_node_1,
       tt.xml_col.value('(/Transaction/Node2) [1]', 'VARCHAR(10)') xml_node_2,       
       tt.xml_col.value('(/Transaction/Node3) [1]', 'VARCHAR(10)') xml_node_3,       
       tt.xml_col.value('(/Transaction/Node4) [1]', 'VARCHAR(10)') xml_node_4,       
       tt.xml_col.value('(/Transaction/Node5) [1]', 'VARCHAR(10)') xml_node_5
FROM   dbo.tmp_xml tt;

-- Drop temp table
DROP TABLE tmp_xml;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s