CODEWITHSUNDEEP
sql-serverxmlt-sqlsql-server-2012xquery
K.M.
K.M.

Reputation: 45

SQL View based on a xml column

My problem is to build a SQL view based on a XML column.

My current table has two columns. One for timestamp and second for data. The data-column is from XML datatype and the content is:

<data>
    <entry header ="Parameter1" value="1" type="System.Boolean"/>
    <entry header ="Parameter2" value="0" type="System.Boolean"/>
    <entry header ="Parameter3" value="140" type="System.int16"/>
    <entry header ="Parameter4" value="80" type="System.int16"/>
    <entry header ="Parameter5" value="530602-455605" type="System.String"/>
</data>

Now I want to create following view:

Timestamp           | Parameter1 | Parameter 2 | Parameter 3 | Parameter 4 | Parameter 5
--------------------+------------+-------------+-------------+-------------+---------------------
2016-10-24 11:30:00 |     1      |      0      |     140     |     80      | 530602-455605

How can I do this?

SELECT TOP 1 
    Timestamp 
    Par1... 
    Par2...
    ParN... 
FROM 
    TableName 
ORDER BY 
    Timestamp DESC

Thanks in advance!

Update

I got the following result (sorry for format):

Timestamp               Header  Value   ValueType

2016-10-24 08:00:00.000 Abguss1 1   System.Boolean

2016-10-24 08:00:00.000 Abguss  0   System.Boolean

2016-10-24 08:00:00.000 Abgussgewicht   140 System.int16

2016-10-24 08:00:00.000 Stopfen 80  System.int16

2016-10-24 08:00:00.000 Werkzeug    530602-455605   System.String

2016-10-24 08:00:20.000 Abguss1 1   System.Boolean

2016-10-24 08:00:20.000 Abguss  0   System.Boolean

2016-10-24 08:00:20.000 Abgussgewicht   138 System.int16

2016-10-24 08:00:20.000 Stopfen 80  System.int16

2016-10-24 08:00:20.000 Werkzeug    530602-455605   System.String

2016-10-24 08:00:40.000 Abguss1 1   System.Boolean

2016-10-24 08:00:40.000 Abguss  0   System.Boolean

2016-10-24 08:00:40.000 Abgussgewicht   178 System.int16

2016-10-24 08:00:40.000 Stopfen 80  System.int16

2016-10-24 08:00:40.000 Werkzeug    530602-455605   System.String

2016-10-24 08:01:20.000 Abguss1 1   System.Boolean

2016-10-24 08:01:20.000 Abguss  0   System.Boolean

2016-10-24 08:01:20.000 Abgussgewicht   158 System.int16

2016-10-24 08:01:20.000 Stopfen 80  System.int16

2016-10-24 08:01:20.000 Werkzeug    530602-455605   System.String

2016-10-24 08:01:43.000 Abguss1 1   System.Boolean

2016-10-24 08:01:43.000 Abguss  0   System.Boolean

2016-10-24 08:01:43.000 Abgussgewicht   162 System.int16

2016-10-24 08:01:43.000 Stopfen 80  System.int16

2016-10-24 08:01:43.000 Werkzeug    530602-455605   System.String

Upvotes: 1

Views: 147

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this

DECLARE @tbl TABLE(ID INT, YourTime DATETIME, YourData XML);
INSERT INTO @tbl VALUES(1,GETDATE(),N'<data>
                                        <entry header ="Parameter1" value="1" type="System.Boolean"/>
                                        <entry header ="Parameter2" value="0" type="System.Boolean"/>
                                        <entry header ="Parameter3" value="140" type="System.int16"/>
                                        <entry header ="Parameter4" value="80" type="System.int16"/>
                                        <entry header ="Parameter5" value="530602-455605" type="System.String"/>
                                    </data>');
SELECT ID 
      ,YourTime
      ,YourData.value('(/data/entry[@header="Parameter1"]/@value)[1]','bit') AS Param1
      ,YourData.value('(/data/entry[@header="Parameter2"]/@value)[1]','bit') AS Param2
      ,YourData.value('(/data/entry[@header="Parameter3"]/@value)[1]','int') AS Param3
      ,YourData.value('(/data/entry[@header="Parameter4"]/@value)[1]','int') AS Param4
      ,YourData.value('(/data/entry[@header="Parameter5"]/@value)[1]','nvarchar(max)') AS Param5
FROM @tbl

UPDATE

Obviously there is a problem with your data... Try the following statement to find all <entry>-elements and try to find, which one disturbs the reading process...

SELECT ID --or TimeStamp-Column or any other row-defining column
      ,e.value('@header','nvarchar(max)') AS Header
      ,e.value('@value','nvarchar(max)') AS Value
      ,e.value('@type','nvarchar(max)') AS ValueType
FROM YourTable
CROSS APPLY YourData.nodes('/data/entry') AS A(e)

My code for reading will pick the value of the entry with the given header name and cast this to the given target type. As long - as you tell me - the long number with the hyphen is sitting in "Parameter5" there cannot be a cast error with bit...

UPDATE 2

Your posted example data shows clearly, that your XML is not called Parameter1 to Parameter5 but with german words. Try it like this:

SELECT ID 
      ,YourTime
      ,YourData.value('(/data/entry[@header="Abguss1"]/@value)[1]','bit') AS Abguss1
      ,YourData.value('(/data/entry[@header="Abguss"]/@value)[1]','bit') AS Abguss
      ,YourData.value('(/data/entry[@header="Abgussgewicht"]/@value)[1]','int') AS Abgussgewicht
      ,YourData.value('(/data/entry[@header="Stopfen"]/@value)[1]','int') AS Stopfen
      ,YourData.value('(/data/entry[@header="Werkzeug"]/@value)[1]','nvarchar(max)') AS Werkzeug
FROM YourTable

Ich wünsche viel Glück beim Lösen der Aufgabe :-)

Upvotes: 2

Related Questions