How to write a custom XML envelope/header

• rikard ekman

  Grasshopper

  Points: 17

  More actions

  June 25, 2008 at 4:18 am

  #208978

  Hello!

  I want to produce this XML output;

  (I have replaced the xml tags with ')

  '?xml version="1.0" encoding="utf-8"?'

  'My_Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" toPartId="MY_SERVER1:P2" xmlns="http://www.my.se/XMLSchemas"'

  'Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.my.se/XMLSchemas"'

  'SetProject'

  'number>L00148</number'

  'name>"some stuff"</name'

  'customerNumber>L00148</customerNumber'

  'customerProjectNumber /'

  'status>open

  'yourReference /'

  'PriceList'

  'hasPriceList>false</hasPriceList'

  '/PriceList'

  'isProjectAccounting>true</isProjectAccounting'

  'isProjectTime>true</isProjectTime'

  'noInvoicing>true</noInvoicing'

  'form /'

  '/SetProject'

  ....

  With this code I get almost want I want;

  --

  WITH XMLNAMESPACES(

  DEFAULT 'http://www.my.se/XMLSchemas',

  'http://www.w3.org/2001/XMLSchema' as "xsd"

  )

  SELECT

  PR01001 as [number],

  PR01009 as name,

  PR01001 as customerNumber,

  '' as customerProjectNumber,

  'open' as [status],

  '' as yourReference,

  '' as PriceList,

  'false' as 'PriceList/hasPriceList',

  'true' as isProjectAccounting,

  'true' as isProjectTime,

  'true' as noInvoicing,

  '' as form

  --'' as subProjectNumber,

  --'' as subProjectName

  --'' as yourReference

  FROM PR010100

  WHERE LEN([PR01001]) = 6 AND [PR01001] LIKE '%_'

  --FOR XMLPATH AUTO

  FOR XML PATH('SetProject'),TYPE,ROOT('My_Message'),ELEMENTS XSINIL

  Result;

  'My_Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xmlns:xsd="http://www.w3.org/2001/XMLSchema"

  xmlns="http://www.my.se/XMLSchemas"'

  'SetProject>

  'number>L00138

  'name>"some stuff..."

  'customerNumber>L00138

  'customerProjectNumber>

  'status>open

  'yourReference>

  'PriceList>

  'hasPriceList>false

  '/PriceList>

  'isProjectAccounting>true

  'isProjectTime>true

  'noInvoicing>true

  'form>

  '/SetProject>

  ---

  ****

  The missing lines are;

  ...

  toPartId="MY_SERVER1:P2" xmlns="http://www.my.se/XMLSchemas">

  'Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.my.se/XMLSchemas">

  ...

  Any Hints or suggestions?

  regards

  Rikard