Complicated xml

• tamarny

  Grasshopper

  Points: 17

  More actions

  June 22, 2015 at 1:41 am

  #305318

  Hi All,

  I will be happy if someone can help me to generate xml

  according to the following data:

  -------Source table-------

  create table #clnt

  (

  client_id bigint

  ,client_descr nvarchar(50)

  ,Phone nvarchar(50)

  )

  insert into #clnt (client_id, client_descr, Phone)

  select 00001, 'TEST CUSTOMER 1', '123-456-789'

  union all

  select 00002, 'TEST CUSTOMER 2', '987-654-321'

  select * from #clnt

  -------Required Results in XML------------

  <?xml version="1.0" encoding="utf-8"?>

  <CustomerAccounts TransactionTimeStamp="2015-04-01T10:50:00Z" PageNumber="1" PageSize="500"

  EndOfFile="true" CreatedLanguage="EN">

  <Customer>

  <Datum UDI="10" Description="Customer ID" Value="00001" Units="CID" />

  <Datum UDI="20" Description="Customer Name" Value="TEST CUSTOMER 1" Units="CUST_NAME" />

  <Datum UDI="30" Description="Phone Number" Value="123-456-789" Units="PHONE_NBR" />

  </Customer>

  <Customer>

  <Datum UDI="10" Description="Customer ID" Value="00002" Units="CID" />

  <Datum UDI="20" Description="Customer Name" Value="TEST CUSTOMER 2" Units="CUST_NAME" />

  <Datum UDI="30" Description="Phone Number" Value="987-654-321" Units="PHONE_NBR" />

  </Customer>

  </CustomerAccounts>

  Thanks,

  Tamar

• Emil B

  SSCertifiable

  Points: 5568

  More actions

  June 22, 2015 at 2:49 am

  #1806959

  Thats the closest i could get, I'm quite sure there's a better way of doing it but couldn't think of anything else ATM.

  SELECT CONVERT(XML, '

  <CustomerAccounts TransactionTimeStamp="2015-04-01T10:50:00Z" PageNumber="1" PageSize="500" EndOfFile="true" CreatedLanguage="EN">' +

  CONVERT(VARCHAR(MAX),(

  SELECT CONVERT(XML,

  '<Datum UDI="10" Description="Customer ID" Value="'+RIGHT('00000'+CONVERT (VARCHAR(6), client_id), 5)+'" Units="CID" />'+

  '<Datum UDI="20" Description="Customer Name" Value="'+client_descr+'" Units="CUST_NAME" />'+

  '<Datum UDI="30" Description="Phone Number" Value="'+Phone+'" Units="PHONE_NBR" />')

  FROM #clnt

  FOR XML PATH('Customer')))

  +'</CustomerAccounts>')

• tamarny

  Grasshopper

  Points: 17

  More actions

  June 22, 2015 at 7:03 am

  #1807016

  Thank you,

  Works great 🙂