解决方案 1

CREATE TABLE #Chart(  StoreID INT PRIMARY KEY,  XMLvalue  XML  );

INSERT INTO #Chart Values (1,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT="1",ASSESSMENT=2,&lt;"ASSESSMENT=3" &gt;</SERIAL><SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL></BETA>')
INSERT INTO #Chart Values (2,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=5,ASSESSMENT=6</SERIAL><SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL></BETA>') 
INSERT INTO #Chart Values (3,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL><SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL></BETA>')
INSERT INTO #Chart Values (4,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=10,ASSESSMENT=11,ASSESSMENT=12,ASSESSMENT=13,ASSESSMENT=14,ASSESSMENT=15</SERIAL><SERIAL NAME="PROJECT"> PROJECT=10,PROJECT=11,PROJECT=12,PROJECT=13</SERIAL></BETA>')


SELECT SToreID,
MAX(CASE WHEN Name = 'ASSESSMENT' THEN LTRIM(RTRIM(Value)) END) AS ASSESSMENT,
MAX(CASE WHEN Name = 'PROJECT' THEN LTRIM(RTRIM(Value)) END) AS PROJECT
FROM
(
SELECT StoreID,u.value('../@NAME','varchar(100)') AS Name,u.query('.').value('.','varchar(max)') AS Value,
ROW_NUMBER() OVER (PARTITION BY SToreID,u.value('../@NAME','varchar(100)') ORDER BY u.query('.').value('.','varchar(max)') ) AS Seq

FROM (
SELECT StoreID,CAST(REPLACE(REPLACE(REPLACE(CAST(XMLValue AS varchar(max)),',','</Value><Value>'),'</SERIAL>','</Value></SERIAL>'),'">','"><Value>')AS xml) AS XMLValue 
FROM  #Chart
) c
CROSS APPLY XMLValue.nodes('/BETA/SERIAL/Value')t(u)
)m
GROUP BY StoreID,Seq
ORDER BY SToreID,Seq


DROP TABLE #Chart

解决方案 2

CREATE TABLE #Chart(  StoreID INT PRIMARY KEY,  XMLvalue  XML  );

INSERT INTO #Chart Values (1,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=0,&lt;/Value&gt;&lt;Value&gt;="1",ASSESSMENT=2,&lt;"ASSESSMENT=3" &gt;</SERIAL><SERIAL NAME="PROJECT"> PROJECT=1,PROJECT=2,PROJECT=3</SERIAL></BETA>')
INSERT INTO #Chart Values (2,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=4,ASSESSMENT=65,ASSESSMENT=16</SERIAL><SERIAL NAME="PROJECT"> PROJECT=4,PROJECT=5,PROJECT=6</SERIAL></BETA>') 
INSERT INTO #Chart Values (3,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=7,ASSESSMENT=8,ASSESSMENT=9</SERIAL><SERIAL NAME="PROJECT"> PROJECT=7,PROJECT=8,PROJECT=9</SERIAL></BETA>')
INSERT INTO #Chart Values (4,'<BETA>  <SERIAL NAME="ASSESSMENT"> ASSESSMENT=10,ASSESSMENT=11,ASSESSMENT=12,ASSESSMENT=13,ASSESSMENT=14,ASSESSMENT=15</SERIAL><SERIAL NAME="PROJECT"> PROJECT=10,PROJECT=11,PROJECT=12,PROJECT=13</SERIAL></BETA>')

go
SELECT * FROM #Chart
go
WITH lists AS (
    SELECT StoreID, 
           assessments = ltrim(A.a.value('(./text())[1]', 'nvarchar(MAX)') COLLATE Latin1_General_BIN),
           projects = ltrim(P.p.value('(./text())[1]', 'nvarchar(MAX)') COLLATE Latin1_General_BIN)
    FROM   #Chart
    CROSS  APPLY XMLvalue.nodes('/BETA/SERIAL[@NAME="ASSESSMENT"]') AS A(a)
    CROSS  APPLY XMLvalue.nodes('/BETA/SERIAL[@NAME="PROJECT"]') AS P(p)
), assess_unwind AS (
   SELECT StoreID, assessments, listpos = 1,
          start = convert(bigint, 1),
          stop = charindex(',', assessments + ',')
   FROM   lists
   UNION ALL
   SELECT StoreID, assessments, listpos + 1, start = stop + 1,
          stop = charindex(',', assessments + ',', stop + 1)
   FROM   assess_unwind 
   WHERE  stop > 0
), proj_unwind AS (
   SELECT StoreID, projects, listpos = 1,
          start = convert(bigint, 1),
          stop = charindex(',', projects + ',')
   FROM   lists
   UNION ALL
   SELECT StoreID, projects, listpos + 1, start = stop + 1,
          stop = charindex(',', projects + ',', stop + 1)
   FROM   proj_unwind 
   WHERE  stop > 0
)
SELECT a.StoreID, 
       substring(a.assessments, a.start, CASE WHEN a.stop > 0 THEN a.stop - a.start ELSE 0 END),
       substring(p.projects, p.start, CASE WHEN p.stop > 0 THEN p.stop - p.start ELSE 0 END)
FROM   assess_unwind a
JOIN   proj_unwind p ON a.StoreID = p.StoreID
                    AND a.listpos = p.listpos
WHERE  a.stop > 0
  AND  p.stop > 0
ORDER BY a.StoreID, a.listpos
OPTION (MAXRECURSION 0)
go
DROP TABLE #Chart