CODEWITHSUNDEEP
sqlsql-serverxmlsoap
Carlos de Sá
Carlos de Sá

Reputation: 3

MS SQL Server query result to XML SOAP envelope

I need to get a XML SOAP envelope as result of a MS SQL Server query, like this one:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:usercontrol">
  <soapenv:Header></soapenv:Header>
  <soapenv:Body>
    <urn:Action>
      <urn:CDUSER>2</urn:CDUSER>
      <urn:IDUSER>jd</urn:IDUSER>
      <urn:NMUSER>John Doe</urn:NMUSER>
      <urn:DTINSERT>2019-09-12</urn:DTINSERT>
    </urn:Action>
  </soapenv:Body>
</soapenv:Envelope>

My query:

WITH XMLNAMESPACES ('urn:usercontrol' AS urn, 'http://schemas.xmlsoap.org/soap/envelope/' AS soapenv)
SELECT
    '' AS [soapenv:Header], 
(
SELECT
    CDUSER AS [urn:CDUSER],
    IDUSER AS [urn:CDUSER],
    NMUSER AS [urn:CDUSER],
    CONVERT(varchar, DTINSERT, 120) AS [urn:DTINSERT]
FROM
    ADUSER
WHERE
    CDUSER = 2
FOR XML PATH('urn:newAction')
) AS [soapenv:Body]
FOR XML PATH('soapenv:Envelope')

Well, the result is not far away, but not what I want:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:usercontrol">
  <soapenv:Header></soapenv:Header>
  <soapenv:Body>&lt;urn:newAction xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:usercontrol"&gt;&lt;urn:CDUSER&gt;2jdJohn Doe&lt;/urn:CDUSER&gt;&lt;urn:DTINSERT&gt;2019-09-12 11:09:45&lt;/urn:DTINSERT&gt;&lt;/urn:newAction&gt;</soapenv:Body>
</soapenv:Envelope>

Can you help me? Many thanks, in advance.

Upvotes: 0

Views: 1016

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

Please try the following solution. It is using XQuery and its FLWOR expression.

It is better to use this approach for a couple of reasons:

  • To prevent namespaces mushrooming in the output XML.
  • Very easy to shape overall XML output structure.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (CDUSER INT, IDUSER VARCHAR(10), NMUSER VARCHAR(20), DTINSERT DATETIME);
INSERT INTO @tbl (CDUSER, IDUSER, NMUSER, DTINSERT) VALUES
(2, 'jd', 'John Doe', GETDATE());
-- DDL and sample data population, end

;WITH XMLNAMESPACES ('urn:usercontrol' AS urn,
                    'http://schemas.xmlsoap.org/soap/envelope/' AS soapenv)
SELECT (
SELECT CDUSER AS [urn:CDUSER],
    IDUSER AS [urn:IDUSER],
    NMUSER AS [urn:NMUSER],
    FORMAT(DTINSERT, 'yyyy-MM-dd') AS [urn:DTINSERT]
FROM @tbl
WHERE CDUSER = 2
FOR XML PATH('r'), TYPE, ROOT('root')
).query('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:usercontrol">
    <soapenv:Header></soapenv:Header>
        <soapenv:Body>
        <urn:Action>
        {
            for $x in /root/r
            return $x/*
        }
        </urn:Action>
    </soapenv:Body>
</soapenv:Envelope>');

Output

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:usercontrol">
  <soapenv:Header />
  <soapenv:Body>
    <urn:Action>
      <urn:CDUSER>2</urn:CDUSER>
      <urn:IDUSER>jd</urn:IDUSER>
      <urn:NMUSER>John Doe</urn:NMUSER>
      <urn:DTINSERT>2021-02-05</urn:DTINSERT>
    </urn:Action>
  </soapenv:Body>
</soapenv:Envelope>

Upvotes: 1

Thom A
Thom A

Reputation: 95589

When writing nested XML statements, you need to ensure your inner queries use the TYPE operator. Otherwise you end up with what you have. This is untested, but it should therefore simply be this

WITH XMLNAMESPACES ('urn:usercontrol' AS urn,
                    'http://schemas.xmlsoap.org/soap/envelope/' AS soapenv)
SELECT '' AS [soapenv:Header],
       (SELECT CDUSER AS [urn:CDUSER],
               IDUSER AS [urn:CDUSER],
               NMUSER AS [urn:CDUSER],
               CONVERT(varchar(20), DTINSERT, 120) AS [urn:DTINSERT]
        FROM ADUSER
        WHERE CDUSER = 2
       FOR XML PATH('urn:newAction'),TYPE) AS [soapenv:Body]
FOR XML PATH('soapenv:Envelope');

Also, get in the habit of defining your length, precision and scales now, so that it doesn't bite you in the foot later.

Upvotes: 0

Related Questions