question

KG3515 avatar image
KG3515 asked

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
sqlxml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
jjaroska avatar image
jjaroska answered
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;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.