CODEWITHSUNDEEP
sql-serverxmlt-sqlxqueryshred
mordeby
mordeby

Reputation: 77

Deserialization XML raw in T-SQL

I have a table in SQL Server. In this table, I have a column Response of datatype XML value. How can I deserialize this XML value like a new table?

My table columns are: CreateDate, Request and Response.

<request-message version="1.0">
<request class="GET_ALL">
    <parameter name="IsInSystem" type="System.Boolean">True</parameter>
    <parameter name="ConfirmDate" type="System.DateTime">2021-05-04 00:00:00.000</parameter>
    <parameter name="ConfirmTime" type="System.DateTime">2021-05-04 00:00:00.000</parameter>
</request>

I want to write this response to a table with parameters as a column.

Upvotes: -1

Views: 92

Answers (1)

marc_s
marc_s

Reputation: 754468

Not entirely sure what you're trying to do - plus the XML shown is invalid (missing end tag for <request-message>).

Maybe something like this?? I'm just iterating over the <parameter> nodes and outputting all relevant info I can think of, as a relational data set:

DECLARE @SomeTable TABLE (ID INT NOT NULL, Request XML)

INSERT INTO @SomeTable (ID, Request)
VALUES (1, '<request-message version="1.0">
<request class="GET_ALL">
    <parameter name="IsInSystem" type="System.Boolean">True</parameter>
    <parameter name="ConfirmDate" type="System.DateTime">2021-05-04 00:00:00.000</parameter>
    <parameter name="ConfirmTime" type="System.DateTime">2021-05-04 00:00:00.000</parameter>
</request></request-message>'    )


SELECT
    ID,
    RequestClass = xc.value('(../@class)[1]', 'varchar(25)'),
    ParamName = XC.value('(./@name)[1]', 'varchar(50)'),
    ParamType = XC.value('(./@type)[1]', 'varchar(50)'),
    ParamValue = XC.value('(./text())[1]', 'varchar(250)')
FROM
    @SomeTable
CROSS APPLY
    Request.nodes('/request-message/request/parameter') AS XT(XC)
WHERE
    ID = 1

This would give you a result something like this:

ID RequestClass ParamName ParamType ParamValue
1 GET_ALL IsInSystem System.Boolean True
1 GET_ALL ConfirmDate System.DateTime 2021-05-04 00:00:00.000
1 GET_ALL ConfirmTime System.DateTime 2021-05-04 00:00:00.000

Upvotes: 0

Related Questions