时间戳

首页 > 解决方案 > MS SQL Server 查询结果到 XML SOAP 信封

sql - MS SQL Server 查询结果到 XML SOAP 信封

问题描述

作为 MS SQL Server 查询的结果，我需要获取一个 XML SOAP 信封，如下所示：

<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>

我的查询：

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')

嗯，结果不远了，但不是我想要的：

<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>

你能帮助我吗？提前谢谢了。

标签： sqlsql-serverxmlsoap

解决方案

请尝试以下解决方案。它使用 XQuery 及其 FLWOR 表达式。

出于以下几个原因，最好使用这种方法：

  • 防止在输出 XML 中出现名称空间。
  • 非常容易塑造整体 XML 输出结构。

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>');

输出

<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>

推荐阅读