CODEWITHSUNDEEP
sqlsql-serverxml
paulmezza
paulmezza

Reputation: 101

Creating XML from MSSQL

How can I create the following XML from MS SQL? I've looked and google and cant see for my specific example as below, thanks. This would be from an SQL query using XML PATH in some manner.

<message>
<header date="15/07/2016" userid="QUOTEJOB">
    <schema name="TKJobLoaderSchema" version="1.0" />
    <source system="" product="" productversion="" />
    <destination system="" product="" productversion="" />
</header>
<body>
    <jobs>
        <job action="jmCreate" company="02" contract="QW" description="test job" job_type="02" priority="5" created_by="QUOTEJOB">
            <job_lines>
                <job_line line_no="1" line_type="SOR" code="AQW" quantity="1916.5" />
            </job_lines>
            <job_narratives>
                <job_narrative id="2" narrative="4678f874-314c-4584-99e3-c69e3af71999" />
            </job_narratives>
            <job_property company="02" ref="02363" />
        </job>
    </jobs>
</body>
</message>

Upvotes: 0

Views: 47

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

Assuming, that every value is 1:1 your given sample can be created like the following (replace the literals with your actual column names, variables, whatever):

SELECT {d'2016-07-15'} AS [header/@date]
      ,'QUOTEJOB' AS [header/@userid]
      ,'TKJobLoaderSchema' AS [header/schema/@name]
      ,'1.0' AS [header/schema/@version]
      ,'' AS [header/source/@system]
      ,'' AS [header/source/@product]
      ,'' AS [header/source/@productversion]
      ,'' AS [header/destination/@system]
      ,'' AS [header/destination/@product]
      ,'' AS [header/destination/@productversion]
      ,'jmCreate' AS [body/jobs/job/@action]
      ,'02' AS [body/jobs/job/@company]
      --more attributes of <job>
      ,1 AS [body/jobs/job/job_lines/job_line/@line_no]
      --more attributes of <job_line>
      ,2 AS [body/jobs/job/job_narratives/job_narrative/@id]
      --more attributes of <job_narrative>
      ,'02' AS [body/jobs/job/job_property/@company]
      ,'02363' AS [body/jobs/job/job_property/@ref]
FOR XML PATH('message')

The result

<message>
  <header date="2016-07-15T00:00:00" userid="QUOTEJOB">
    <schema name="TKJobLoaderSchema" version="1.0" />
    <source system="" product="" productversion="" />
    <destination system="" product="" productversion="" />
  </header>
  <body>
    <jobs>
      <job action="jmCreate" company="02">
        <job_lines>
          <job_line line_no="1" />
        </job_lines>
        <job_narratives>
          <job_narrative id="2" />
        </job_narratives>
        <job_property company="02" ref="02363" />
      </job>
    </jobs>
  </body>
</message>

Upvotes: 1

Related Questions