Parse XML to TABLE

Question

I am new to XML use inside SQL Fields. I have the following field example (can contain anywhere from 12 - 30 variables) that I need to convert the contents of this field for all rows into a table with headers. I have read a slew of forums and can't find anything that does what I'm looking for - maybe I am using the wrong terms? Any advice would be greatly appreciated. ---SAMPLE--- (the leading "(" is really a < but the HTML generator strips out the code if I leave it in) (DSD>
(GROUP LABEL=""All"">
(VARIABLE INDEX=""0"" LABEL=""Tag"" VALUE=""GENERIC"" UNIT="""" DMS_IDENTITY=""TAG""/>
(VARIABLE INDEX=""1"" LABEL=""Descriptor"" VALUE="" "" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""2"" LABEL=""Message"" VALUE="" "" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""3"" LABEL=""Date"" VALUE=""10-25-2002"" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""4"" LABEL=""Units"" VALUE=""InH2O"" UNIT="""" DMS_IDENTITY=""PV_UNITS""/>
(VARIABLE INDEX=""5"" LABEL=""Damping Value"" VALUE=""0.000"" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""6"" LABEL=""Transfer Function"" VALUE=""linear"" UNIT="""" DMS_IDENTITY=""PV_XFR""/>
(VARIABLE INDEX=""7"" LABEL=""Input Upper Range Value"" VALUE=""0.000"" UNIT="""" DMS_IDENTITY=""PV_URV""/>
(VARIABLE INDEX=""8"" LABEL=""Input Lower Range Value"" VALUE=""0.000"" UNIT="""" DMS_IDENTITY=""PV_LRV""/>
(VARIABLE INDEX=""9"" LABEL=""Output Upper Range Value"" VALUE=""20.000"" UNIT="""" DMS_IDENTITY=""PV_OURV""/>
(VARIABLE INDEX=""10"" LABEL=""Output Lower Range Value"" VALUE=""4.000"" UNIT="""" DMS_IDENTITY=""PV_OLRV""/>
(VARIABLE INDEX=""11"" LABEL=""Output Units"" VALUE=""mA"" UNIT="""" DMS_IDENTITY=""PV_OUNITS""/>
(VARIABLE INDEX=""12"" LABEL=""Final Assy Number"" VALUE=""0"" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""13"" LABEL=""Snsr SN"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""14"" LABEL=""Module Range"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""15"" LABEL=""Pres Min Span"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""16"" LABEL=""Flange Type"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""17"" LABEL=""Flange Mtrl"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""18"" LABEL=""Isoltr Mtrl"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""19"" LABEL=""0 ring Mtrl"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""20"" LABEL=""Drain vent Mtrl"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""21"" LABEL=""Fill Fluid"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""22"" LABEL=""RS SN"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""23"" LABEL=""RS Type"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""24"" LABEL=""RS Isoltr Mtrl"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""25"" LABEL=""RS Fill Fluid"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(VARIABLE INDEX=""26"" LABEL=""Num RS"" VALUE="""" UNIT="""" DMS_IDENTITY=""""/>
(/GROUP>
(/DSD> expected result: Tag Descriptor Message Date Units DampingValue (etc) GENERIC NULL NULL 10-25-2002 InH20 0.000

Answer 1

Try this. However, there are 2 things you must do. You must do a global replace on "variable index" and replace with "row variableindex" You need the row indicator to get the row right and the script chokes on spaces in the column names.

BEGIN TRAN;

DECLARE
   @tempTable TABLE (
                    VARIABLEINDEX nvarchar (50) 
                  , LABEL nvarchar (50) 
                  , VALUE nvarchar (50) 
                  , UNIT nvarchar (50) 
                  , DMS_IDENTITY nvarchar (50)) ;
DECLARE
   @xml xml;
SET @xml = '

';

INSERT INTO @tempTable
SELECT
       Tbl.Col.value ('@VARIABLEINDEX', 'NVARCHAR(50)') 
     , Tbl.Col.value ('@LABEL', 'NVARCHAR(50)') 
     , Tbl.Col.value ('@VALUE', 'NVARCHAR(50)') 
     , Tbl.Col.value ('@UNIT', 'NVARCHAR(50)') 
     , Tbl.Col.value ('@DMS_IDENTITY', 'NVARCHAR(50)') 
FROM @xml.nodes ('//row') AS Tbl (Col) ;

--See the table
SELECT
       *
FROM @tempTable;

ROLLBACK;

Parse XML to TABLE

Follow this question

Related Questions