如何从Postgres生成高级XML

Question

如何在Postgres中生成类似的XML ?入组日期、性别和研究标识符将来自数据库中的查询。

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v1="http://openclinica.org/ws/studySubject/v1" xmlns:bean="http://openclinica.org/ws/beans">
   <soapenv:Header>
<wsse:Security soapenv:mustUnderstand="1"
 xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
<wsse:UsernameToken wsu:Id="UsernameToken-27777511"
 xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<wsse:Username>userName</wsse:Username>
<wsse:Password>someHashedPassword/wsse:Password>
</wsse:UsernameToken>
</wsse:Security>
</soapenv:Header>
   <soapenv:Body>
      <v1:createRequest>
         <v1:studySubject>
            <!--Optional:-->
            <bean:label>CBID01</bean:label>
            <bean:enrollmentDate>2016-10-28</bean:enrollmentDate>
            <bean:subject>
               <!--Optional:-->
               <bean:gender>f</bean:gender>
            </bean:subject>
            <bean:studyRef>
               <bean:identifier>Clinical_AutoID</bean:identifier>
            </bean:studyRef>
         </v1:studySubject>
      </v1:createRequest>
   </soapenv:Body>
</soapenv:Envelope>

Answer 1

我就能够生成XML文件了。但如果有人知道更优雅的方法，请告诉我。PS:我去掉了标签字段，因为系统会自动生成一个。

select xmlelement(name "soapenv:Envelope", 
        xmlattributes('http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soapenv",
                 'http://openclinica.org/ws/studySubject/v1' AS "xmlns:v1",
                 'http://openclinica.org/ws/beans' AS "xmlns:bean"), 
        xmlelement(name "soapenv:Header", 
            xmlelement(name "wsse:Security", 
                xmlattributes(1 AS "soapenv:mustUnderstand", 
                    'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd' AS "xmlns:wsse"),
                xmlelement(name "wsse:UsernameToken",
                        xmlattributes('UsernameToken-27777511' AS "wsu:Id",
                            'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd' AS "xmlns:wsu"),
                xmlelement(name "wsse:Username", 'userName'),
                xmlelement(name "wsse:Password",'someHashedPassword')))),
            xmlelement(name "soapenv:Body",
            xmlelement(name "v1:createRequest",
                xmlelement(name "v1:studySubject",
                    xmlelement(name "bean:enrollmentDate", '2016-10-28'),
                    xmlelement(name "bean:subject",
                        xmlelement( name "bean:gender", 'f')),
                    xmlelement(name "bean:studyRef",
                        xmlelement(name "bean:identifier", 'Clinical_AutoID'))
                    )
                )
            )
        )
    )