Skip to content

06 Working with JSON and XML

This example stores JSON as XML in SQL Server and reads it back as JSON:

  • opts="json-xml" on the parameter converts the JSON value to XML; it needs a JSON body (POST)
  • type="tjson" on the column converts the XML from the database back to JSON

To produce an XML file from query results, use the XML export renderer instead.

POST JSON > API > XML > DB

HTTP
1
2
3
4
curl '{{host}}/api/srs/333-test/run.json' \
  -H 'Content-Type: application/json' \
  --user 'api:{{token}}' \
  --data-raw '{"xmldatainput":"{\"test\":\"[email protected]\" }","operation":"post"}'

GET DB > XML > API > JSON

HTTP
1
2
3
4
curl '{{host}}/api/srs/333-test/run.json' \
  -H 'Content-Type: application/json' \
  --user 'api:{{token}}' \
  --data-raw '{"operation":"get"}'

Example

SRS

XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
<srs label="test">
  <def>
    <itm model="param" name="operation"/>
    <itm model="param" name="xmldatainput" type="xml" opts="json-xml"/>
    <itm model="column" name="xmldata" type="tjson"/>
    <itm name="post">
      if @operation='post'
        insert into testxml (xmldata)  values (@xmldatainput)
    </itm>
    <itm name="get">  
      if @operation='get'
            select * from testxml
    </itm>
  </def>
</srs>

DB

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE [dbo].[testxml](
    [id] [uniqueidentifier] NOT NULL,
    [xmldata] [xml] NULL,
 CONSTRAINT [PK_testxml] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[testxml] ADD  CONSTRAINT [DF_testxml_id]  DEFAULT (newid()) FOR [id]