Convert Delimited XML value in a column into Column (More than 8000 characters)
To handle more than 8000 characters, convert that data into XML format and insert into temp table.Use this temp table wherever we want.
Step 1:
First convert your data into XML format.
If is comma separated varchar value, then go step 2.
Step 2:
For delimited value run below query.
select stuff((select distinct ','+cast(AddressID as varchar(max))
from Person.Address for xml path('') ),1,1,''))
Here my delimiter is ,(comma). I want to convert into XML.
Declare @Xml AS XML
SET @Xml =
( SELECT CAST (N'<r>' + REPLACE((select stuff((select distinct ','+cast(AddressID as varchar(max))
from Person.Address for xml path('') ),1,1,'')), ',', '</r><r>') + '</r>' AS XML) AS [vals] )
select @Xml
the selected XML value comes like below.
<r>29180</r>
<r>27611</r>
<r>22762</r>
<r>16843</r>
<r>17333</r>
<r>17665</r>
<r>24578</r>
<r>21802</r>
<r>26564</r>
<r>13205</r>
<r>499</r>
Convert that xml into table format.
SELECT A.value('.', 'varchar(max)') as AssessmentID FROM @Xml.nodes('r') AS FN(A) order by AssessmentID
The above query will give you table result. Insert insert this into temp table.
Below are the different ways to read the XML data in table format.
use [AdventureWorks2012]go
DECLARE @XMLPath xml
set @XMLPath =(SELECT top 10 AddressID FROM Person.Address ORDER BY AddressID FOR XML PATH(''))
select @XMLPath
SELECT A.value('.', 'int') as AddressID FROM @XMLPath.nodes('AddressID') AS FN(A) order by AddressID
set @XMLPath =(SELECT top 10 AddressID FROM Person.Address ORDER BY AddressID FOR XML RAW('AddressIDs'))
SELECT A.value('@AddressID', 'int') as AddressID FROM @XMLPath.nodes('AddressIDs') AS FN(A) order by AddressID
select @XMLPath
Read one of the tag in XML format.
declare @DateXml xml =
'<Settings>
<NumOfRp>A</NumOfRp>
<InsID>ABCD</InsID>
<Terms>
<Term><Rpid>A</Rpid><MonthName>January</MonthName><Startdate>03/20/2015</Startdate><Enddate>03/24/2015</Enddate><Sortorder>1</Sorder></Term>
</Terms>
<YourComments>
<CommentSettings>
<AllPermitted>Y</AllPermitted><Predefined>Y</Predefined><Custom>Y</Custom><Personalized>N</Personalized><Century21st>Y</Century21st>
<Unlimited>Y</Unlimited>
</CommentSettings>
</YourComments>
</Settings>'
Now I want to take one of the tag CommentSettings as a XML data only.
SELECT
Comments.query('.')
AS Comments
FROM
@DateXml.nodes('/Settings/Comments/RCCommentSettings')
AS xmlData(Comments);
SELECT
Comments.query('.')
AS Comments
FROM
@DateXml.nodes('/Settings/Comments')
AS xmlData(Comments);
this XML data you may store in database.
