Creating XML from MSSQL

Question

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>

Is there any 1:n nested data in deeper level? This sample looks nested but plain 1:1... — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 16, 2017 at 16:15
Where is the data coming from? table, literals, variables...? — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 16, 2017 at 16:15
Hi,sorry ive updated the question. it was to do it from an sql query and make the xml structure as it is above with message as the root, and header and body inside that, each with there attributes and further levels inside. all the examples I can find are where you would have message, then maybe header as repeatable, and no other separate sections. and no I just needs to be as above, no deeper nesting etc — paulmezza
– paulmezza, Commented Mar 16, 2017 at 16:19

Gottfried Lesigang · Accepted Answer · 2017-03-17 19:29:48Z

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>

Collectives™ on Stack Overflow

Creating XML from MSSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related