x

Parse XML to TABLE

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
more ▼

asked Sep 17, 2012 at 01:18 PM in Default

KG3515 gravatar image

KG3515
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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;
more ▼

answered Sep 19, 2012 at 06:46 PM

jjaroska gravatar image

jjaroska
170 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x713
x146

asked: Sep 17, 2012 at 01:18 PM

Seen: 850 times

Last Updated: Sep 20, 2012 at 01:33 PM