CODEWITHSUNDEEP
xmlsql-server-2008sqlxml
James A Mohler
James A Mohler

Reputation: 11120

XML with content between tags

I have SQL

SELECT '' AS "dfn", CCFKeyType AS "dfn/b", GroupID AS "dfn/var",  ID AS "dfn/code"
FROM (
...
) A

FOR XML PATH('')

AND it generates

<dfn>
   <b>cid</b>
   <var>0115</var>
   <code>EXS</code>
</dfn>

I would like to generate:

<dfn>
   <b>cid</b>
   (<var>0115</var>):
   <code>EXS</code>
</dfn>

How can text data be inseted between tags?

Upvotes: 0

Views: 30

Answers (1)

Rubens Farias
Rubens Farias

Reputation: 57996

SELECT  ''     AS "dfn"    , 
        'cid'  AS "dfn/b"  ,
        '('    AS "dfn"    ,
        '0115' AS "dfn/var",
        '):'   AS "dfn"    ,
        'EXS'  AS "dfn/code"
FOR XML PATH('')

Output:

<dfn>
    <b>cid</b>
    (<var>0115</var>):
    <code>EXS</code>
</dfn>

Upvotes: 1

Related Questions