Archive for the ‘SQL Server 2005 – XML’ Category

XML Special Characters

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

If you use XML with no DTD (Document Type Definitions), the following five characters are assumed to be predeclared, and you can use them without declaring them as follows:

&lt; The less-than character (<)
&amp; The ampersand character (&)
&gt; The greater-than character (>)
&quot; The double-quote character (")
&apos; The apostrophe or single-quote character (')

You can get these characters in the XML file as follows:

-- Declare variables
DECLARE @lxml_value XML;

-- Set XML values
SET @lxml_value = '<Detail>
                      <Name>Testing XML &amp; Special Characters</Name>
                      <Address>Test XML&apos;s address</Address>
                      <Quote>&quot;Quote&quot;</Quote>
                      <Compare1>10 &lt; 20</Compare1>
                      <Compare2>10 &gt; 5</Compare2>
                   </Detail>'

-- Select vales from XML
SELECT @lxml_value.value('(/Detail/Name)     [1]', 'VARCHAR(100)');
SELECT @lxml_value.value('(/Detail/Address)  [1]', 'VARCHAR(100)');
SELECT @lxml_value.value('(/Detail/Quote)    [1]', 'VARCHAR(100)');
SELECT @lxml_value.value('(/Detail/Compare1) [1]', 'VARCHAR(100)');
SELECT @lxml_value.value('(/Detail/Compare2) [1]', 'VARCHAR(100)');
Advertisements

Update XML Node

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

If you need to change the value of an XML node, you can try doing the following:

-- Create a schema for your XML data
CREATE XML SCHEMA COLLECTION my_xml_schema
AS
'<xsd:schema elementFormDefault="unqualified" attributeFormDefault="unqualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
    <xsd:element name="Transaction">
       <xsd:complexType>
          <xsd:sequence>
             <xsd:element name="Type" type="xsd:string"/>
             <xsd:element name="Code" type="xsd:string"/>
          </xsd:sequence>                      
       </xsd:complexType>
    </xsd:element>
 </xsd:schema>'
GO

-- Declare variables
DECLARE @lv_type   VARCHAR(10);
DECLARE @lxml_data XML(my_xml_schema); -- Declare this using the schema created above

-- Set change value
SET @lv_type = 'Change';

-- Set the XML data
SET @lxml_data = '<Transaction>
                     <Type>Node Type</Type>
                     <Code>Node Code</Code>
                  </Transaction>';

-- Check XML data
SELECT @lxml_data;

-- Modify the XML data
SET @lxml_data.modify('replace value of (/Transaction [1]/Type [1]) with sql:variable("@lv_type")');

-- Check XML data
SELECT @lxml_data;

-- Drop XML schema
DROP XML SCHEMA COLLECTION my_xml_schema
GO

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;

To select records from a table into XML format, open a new query window and execute the following line of code:

-- Get a records from table into XML format
SELECT  *
FROM    
FOR XML AUTO, ROOT;
<code></code>
-- The above select will return rows in the following structure
-- <root>
--     <TableName ColumnName="ColumnValue" />
-- </root>

To select a result set and return the results in a single concatenated string, open a new query window and execute the following line of code:

-- Create a new test table
CREATE TABLE [MyTest] ([TestValue] VARCHAR(10));

-- Insert test data into the table
INSERT INTO [MyTest] VALUES ('A Test');
INSERT INTO [MyTest] VALUES ('B Test');
INSERT INTO [MyTest] VALUES ('C Test');
INSERT INTO [MyTest] VALUES ('D Test');
INSERT INTO [MyTest] VALUES ('E Test');

-- Declare variables
DECLARE @Result    VARCHAR(MAX);
DECLARE @Space     VARCHAR(1);
DECLARE @Delimiter VARCHAR(1);

-- Initialise varibales
SET @Result    = '';
SET @Space     = '|';
SET @Delimiter = ',';

-- Use XML structure to output values into a single string
SELECT @Result = REPLACE
                 (
                  (SELECT   REPLACE([TestValue], ' ', @Space) AS [data()]
                   FROM     [MyTest]
                   ORDER BY [TestValue]
                   FOR XML PATH ('')
                  ), ' ', @Delimiter
                 );

-- Replace the '|' characters with spaces again
SELECT @Result = REPLACE(@Result, @Space, ' ');

-- Output the result
PRINT '';
PRINT @Result;

-- Drop the table
DROP TABLE MyTest;