MS SQL Server query result to XML SOAP envelope

Question

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.

Yitzhak Khabinsky · Accepted Answer · 2021-02-05 16:54:13Z

1

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>

edited Feb 5, 2021 at 16:54

answered Feb 5, 2021 at 16:47

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carlos de Sá Over a year ago

I'm so grateful to to you, Yitzhak. Your solution is working fine.

Yitzhak Khabinsky Over a year ago

@CarlosdeSá, glad to hear that the proposed solution is working for you. Please don't forget to mark it as Answer.

Thom A · Accepted Answer · 2021-02-05 15:00:06Z

0

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.

answered Feb 5, 2021 at 15:00

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

2 Comments

Carlos de Sá Over a year ago

Thank you, very much, Larnu.

Carlos de Sá Over a year ago

Thank you, very much, Larnu. Your solution works, but repeats the namespaces in the tag urn:newAction. The solution of Yitzhak, above, works fine.

Collectives™ on Stack Overflow

MS SQL Server query result to XML SOAP envelope

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related