A simple cast will suffice:
select cast(XMLCol as nvarchar(max)) as XMLCol
Or for non-unicode:
select cast(XMLCol as varchar(max)) as XMLCol
You can't convert explicitly to a 'text' data type.
I've added the as XMLCol to ensure that the converted data has the the same name as the column. You needn't have this, of course.
EDIT:
A few links. You are encouraged to use nvarchar(max) instead of text regardless. Microsoft have said they will be deprecating these types in future releases. nvarchar(max) ought to offer you 2GB:
http://www.petefreitag.com/item/734.cfm
http://www.teratrax.com/articles/varchar_max.html
http://msdn.microsoft.com/en-us/library/ms187752(v=SQL.90).aspx
Answer from Mutation Person on Stack Overflowsql server - ms sql xml data type convert to text - Stack Overflow
sql server - T-SQL: Convert XML to string without self-closing tags - Database Administrators Stack Exchange
sql server - How to create xml string for each row of table? - Database Administrators Stack Exchange
Using SQL Server "FOR XML": Convert Result Datatype to Text/varchar/string whatever? - Stack Overflow
How do you convert XML to a string in SQL Server?
Use CAST or CONVERT to transform an XML-typed value to a string: SELECT CAST(XmlColumn AS NVARCHAR(MAX)) FROM YourTable. For the reverse (string to XML), use CAST(StringColumn AS XML). Note that CAST to NVARCHAR(MAX) preserves the full XML content, while NVARCHAR(4000) may truncate large documents. You can also use the .value() method with a specific XPath to extract individual text values directly.
Should you store XML in an XML column or VARCHAR in SQL Server?
Always prefer the native XML data type over VARCHAR. XML-typed columns provide automatic validation (ensuring only well-formed XML is stored), support for XQuery methods (.value, .query, .nodes, .modify), XML-specific indexing, and schema collection binding. Storing XML as VARCHAR loses all these benefits and forces frequent CAST operations. The only exception is when XML content will never be queried by SQL Server and is purely written/read by the application.
What is the fastest way to parse XML in SQL Server?
For querying individual values from XML, the .value() XQuery method is generally fastest. For shredding XML into rows, the .nodes() method outperforms OPENXML for most use cases. OPENXML with sp_xml_preparedocument is better suited for very large documents processed once. For repeated queries against the same XML column, create a primary XML index followed by secondary indexes (PATH, VALUE, or PROPERTY) matching your query patterns. Avoid repeated CAST operations between XML and string types.
Videos
A simple cast will suffice:
select cast(XMLCol as nvarchar(max)) as XMLCol
Or for non-unicode:
select cast(XMLCol as varchar(max)) as XMLCol
You can't convert explicitly to a 'text' data type.
I've added the as XMLCol to ensure that the converted data has the the same name as the column. You needn't have this, of course.
EDIT:
A few links. You are encouraged to use nvarchar(max) instead of text regardless. Microsoft have said they will be deprecating these types in future releases. nvarchar(max) ought to offer you 2GB:
http://www.petefreitag.com/item/734.cfm
http://www.teratrax.com/articles/varchar_max.html
http://msdn.microsoft.com/en-us/library/ms187752(v=SQL.90).aspx
SELECT CAST(YourXMLColumn as nvarchar(max))
FROM YourTable
Strictly speaking, a self-closing tag <summary /> and a full tag <summary></summary> have the same meaning in XML, so it shouldn't make a difference to you, or to any proper XML parser.
But if you want to force it to generate a full tag as opposed to a self-closing one, you can add an empty text() column:
SELECT (
SELECT
e.id AS [@id],
e.designation AS [@type],
e.salary AS [@salary],
'' AS [text()]
FOR XML PATH('summary')
)
FROM employees e;
db<>fiddle
You don't need to self-join in the subquery, you can refer to the outer columns directly. Note also that you should use [] to quote columns where necessary, not ''.
Side note: you don't need to store this XML, you can create a view that brings it up when you query it.
I would recommend not doing this at all.
All the data values in the XML are present in other fields within the record, which makes the XML a derived field.
General Rule: do not store Data that you can derive from other Data.
Instead, build the XML "on demand", in a Query, or in a View or in a calculated column.
At its simplest (albeit most likely to break if XML-meaningful characters aren't properly escaped):
select '<summary'
|| ' id='\'' || id || '\'''
|| ' type='\' || designation || '\''
|| ' salary='\' || salary || '\''
|| '\'/>' xml
from table1
And, as others have said, <summary></summary> and <summary/> are completely equivalent in XML. (To [X]HTML? Perhaps not, but to XML itself? Yes).
Going out on a limb here with two assumptions; your question isn't clear about the following:
- I'm assuming that your child nodes have the same name (e.g., child, not child1 and child2), and
- You want a SQL statement that returns 1 child per row.
If either of those assumptions is incorrect, this answer won't help :)
DECLARE @xml XML = '<Grandparent>
<parent>
<child>something</child>
<child>something</child>
</parent>
</Grandparent>'
SELECT x.value('.[1]', 'varchar(100)')
FROM @xml.nodes('/Grandparent//child') t(x)
SET @xml= '<Grandparent>
<child>something</child>
<child>something</child>
</Grandparent>'
SELECT x.value('.[1]', 'varchar(100)')
FROM @xml.nodes('/Grandparent//child') t(x)
You can use descendant axis // to get child nodes at any level depth within a parent node.
Another useful xpath syntax for this task is local-name() which return current context node/attribute's name without namespace :
select c.value('local-name(.)', 'varchar(max)') as 'node'
, c.value('.', 'varchar(max)') as 'value'
from @xml.nodes('/Grandparent//*[not(*)]') as T(c)
This xpath bit //*[not(*)] means select descendant nodes that doesn't have child node, in other words select the inner most descendant.
SQL Fiddle
I have below sql table. I need to add a column `xmls` to my table such that the table looks like below.
CREATE TABLE employees(
id integer,
name VARCHAR(MAX),
designation VARCHAR(MAX),
manager integer,
hired_on date,
salary integer,
commission float,
dept integer);
INSERT INTO employees VALUES (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4);
INSERT INTO employees VALUES (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3);
INSERT INTO employees VALUES (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3);
INSERT INTO employees VALUES (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3);
SELECT * FROM employees;The new column xmls should look like below(last column below), I know I can do it with the help of string utilities but cannot figure the simple yet effective and optimal solution.
This is what I tried but this does not give me the end tag `summary`.
SELECT (
SELECT id AS '@id',
designation AS '@type',
salary AS '@salary'
FROM employees b
WHERE b.id = a.id
FOR XML PATH('summary'))
FROM employees a You should look at the actual XML in your table. If you insert that xml into a table it is not the same string you think it is.
declare @Something table(baserentamount xml)
insert @Something select '<X C="1"></X>'
select *
, cast(baserentamount as varchar(250))
from @Something
Therefore your query will very likely return every row. And it will certainly return the rows you want to exclude because the xml string has changed.
Ahhh, well there it is, the devil is in the details:
XML as presented:
<X C="1" />
XML after CAST:
<X C="1"/>
Not entirely obvious, nor obvious why this should be, but there it is, it's a WS problem.
If Queries against the XML will happen by sql server xml capabilities, then use XML type to store a xml to avoid casting
And
keep in mind, that XML type may be stored little bit slower due to xml validation, but underlying type of XML is ordinary varbinary(max)
what factors should I be considering when trying to decide between storing XML in an
xmlcolumn vs. avarchar(MAX)column
The factors are:
The
XMLtype is queryable / parseable through XQuery expressions, including being able to use FLWOR Statement and IterationData in
XMLvariables and columns can be modified inline using XQuery expressions via XML DML.XMLdata is stored as UTF-16 LE (Little Endian), soVARCHAR(MAX)would be a poor choice as it could result in data loss. Hence, the true decision should be betweenXMLandNVARCHAR(MAX), given thatNCHAR/NVARCHARis also UTF-16 LE.XMLdata can be validated against an XSD /XML SCHEMA COLLECTION. No validation (outside of ensuring well-formedness) is done if no XML Schema Collection is specified, but this option is not available when usingNVARCHAR(MAX).One major benefit of the XML type is that it is stored in a highly optimized format (not
VARBINARY(MAX)as stated in @Oleg's answer) that does not store the exact string representation that you see, but instead has a dictionary of Element and Attribute names and refers to them by their ID. It also removes whitespace. Try the following:DECLARE @Test1 XML = N'<Test><TagName>1</TagName><TagName>2</TagName></Test>'; DECLARE @String1 NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @Test1); SELECT DATALENGTH(@Test1) AS [XmlBytes], LEN(@String1) AS [StringCharacters], DATALENGTH(@String1) AS [StringBytes]; SET @Test1 = N'<Test><TagName>1</TagName><TagName>2</TagName><TagName>3</TagName> <TagName>4</TagName><TagName>5</TagName><TagName>6</TagName></Test>'; SET @String1 = CONVERT(NVARCHAR(MAX), @Test1); SELECT DATALENGTH(@Test1) AS [XmlBytes], LEN(@String1) AS [StringCharacters], DATALENGTH(@String1) AS [StringBytes];Returns:
XmlBytes StringCharacters StringBytes 56 53 106 XmlBytes StringCharacters StringBytes 84 133 266As you can see in the example output above, adding four elements (#s 3, 4, 5, and 6) added 80 characters (hence 80 bytes if using
VARCHAR) and 160 bytes to theNVARCHARvariable. Yet, it only added 28 bytes to the XML variable, which is less than it added forVARCHAR(just in case someone was going to argue in favor ofVARCHARoverXMLbecauseXMLis UTF-16 which is [mostly] double-byte). This optimization can save tons of space, and is reason enough by itself to use theXMLdatatype.XML data can be indexed via specialized XML indexes