This query shows how to extract an array of values for a field inside of the XML string:
DECLARE @sInputList VARCHAR(MAX) DECLARE @sDelimiter VARCHAR(MAX) DECLARE @sDelimiter2 VARCHAR(MAX) SET @sInputList = '<package><document copies="1" docgroupid="6" docid="1042" packagedocid="docd1042-0" rule="" selected="true" title="Document 1 - Doc Group: Group 1" xmlformid="-1"><document copies="1" docgroupid="4" docid="2189" packagedocid="docd2189-0" rule="" selected="true" title="Document 2 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="78" packagedocid="docd78-0" rule="" selected="true" title="Document 3 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="2122" packagedocid="docd2122-0" rule="" selected="true" title="Document 4 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="10" docid="2057" packagedocid="docd2057-0" rule="" selected="true" title="Document 5 - Doc Group: Package" xmlformid="-1"><document copies="1" docgroupid="10" docid="2055" packagedocid="docd2055-0" rule="" selected="true" title="Document 6 - Doc Group: Package" xmlformid="-1"><document copies="1" docgroupid="5" docid="873" packagedocid="docd873-0" rule="" selected="true" title="Document 7 - Doc Group: Group 3" xmlformid="-1"><document copies="1" docgroupid="5" docid="246" packagedocid="docd246-0" rule="" selected="true" title="Document 8 - Doc Group: Group 4" xmlformid="-1"><document copies="1" docgroupid="4" docid="1843" packagedocid="docd1843-0" rule="" selected="true" title="Document 9 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="621" packagedocid="docd621-0" rule="" selected="true" title="Document 10 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="230" packagedocid="docd230-0" rule="" selected="true" title="Document 11 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="2152" packagedocid="docd2152-0" rule="" selected="true" title="Document 12 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="2002" packagedocid="docd2002-0" rule="" selected="true" title="Document 13 - Doc Group: Group 2" xmlformid="-1"><document copies="1" docgroupid="4" docid="2153" packagedocid="docd2153-0" rule="" selected="true" title="Document 14 - Doc Group: Group 2" xmlformid="-1"></document></document></document></document></document></document></document></document></document></document></document></document></document></document></package>' SET @sDelimiter = ' docID="' SET @sDelimiter2 = '"' IF CHARINDEX(@sDelimiter,@sInputList,0) <> 0 SET @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) --) RETURNS @List TABLE (item VARCHAR(8000)) DECLARE @List TABLE (item VARCHAR(MAX)) DECLARE @sItem VARCHAR(8000) WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0 BEGIN SET @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))) IF CHARINDEX(@sDelimiter2,@sItem,0) <> 0 SET @sItem=RTRIM(LTRIM(SUBSTRING(@sItem,1,CHARINDEX(@sDelimiter2,@sItem,0)-1))) SET @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) IF LEN(@sItem) > 0 INSERT INTO @List SELECT @sItem END IF LEN(@sInputList) > 0 BEGIN SET @sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter2,@sInputList,0)-1))) INSERT INTO @List SELECT @sInputList -- Put the last item in END SELECT * FROM @List
