Link to home
Start Free TrialLog in
Avatar of Vouchagram India
Vouchagram IndiaFlag for India

asked on

MS SQL Database Generating XML using query

Hi Team,

I am looking to generate XML as per attached format. There are few elements which should be hardcode values in each XML node like
reqtype, MopId,companycode,UploadType,UploadDetail,UploadedBy,StoreNo & guid. Rest of the elements should be retrieved via query.

Please help to let me know what type of query should i write to get the desired output

Rajneesh
XML.txt
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
Dont have your data and query.. sample would be..

 SELECT
            col1,
            col2,
			col3
        FROM
            dbo.yourtablename a        
        FOR XML PATH ('uploadGVreq'), TYPE, ROOT ('list_input')

Open in new window

Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

Please check the attachment sent by me. I am running below query
but the tag missing gvupload and GUID should be before closing of gvupload tag.

select voucherid as 'sequenceNumber',VOUCHERNO as 'cardNumber',
'1' as 'reqtype','15' as 'MopId',
REDEMPTIONFACEVALUE as 'amount','1' as 'flag',  
VOUCHEREXPIRYDATE as 'expdate',
blastdate as 'UploadDate','VG' as 'companycode', '' 'UploadType',
'' 'UploadDetail','' 'UploadedBy',
'' 'StoreNo','' 'PIN','5111a4c9-5007-4367-a4a1-071a2cfa1b26' GUID
from vgt_voucher a, vgm_product b
where a.PRODUCTID=b.PRODUCTID
and a.MERCHANTID=8      
and BLASTDATE>='11/30/2016 00:00:00'
and BLASTDATE<='11/30/2016 23:59:59'
FOR XML PATH ('uploadGVreq'), TYPE, ROOT ('list_input')
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
Send what you are getting with the above query ?
Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

<list_input>
  <uploadGVreq>
    <sequenceNumber>1874097</sequenceNumber>
    <cardNumber>TGIF533112</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874098</sequenceNumber>
    <cardNumber>TGIF599711</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874099</sequenceNumber>
    <cardNumber>TGIF626226</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874100</sequenceNumber>
    <cardNumber>TGIF926171</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874101</sequenceNumber>
    <cardNumber>TGIF722444</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874102</sequenceNumber>
    <cardNumber>TGIF173171</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874103</sequenceNumber>
    <cardNumber>TGIF534453</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874104</sequenceNumber>
    <cardNumber>TGIF233153</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874105</sequenceNumber>
    <cardNumber>TGIF415322</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874106</sequenceNumber>
    <cardNumber>TGIF556292</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874107</sequenceNumber>
    <cardNumber>TGIF455232</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874108</sequenceNumber>
    <cardNumber>TGIF666397</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874109</sequenceNumber>
    <cardNumber>TGIF592951</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874110</sequenceNumber>
    <cardNumber>TGIF279992</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874111</sequenceNumber>
    <cardNumber>TGIF156169</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874112</sequenceNumber>
    <cardNumber>TGIF713426</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874113</sequenceNumber>
    <cardNumber>TGIF993159</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874114</sequenceNumber>
    <cardNumber>TGIF419296</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874115</sequenceNumber>
    <cardNumber>TGIF212157</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>1874116</sequenceNumber>
    <cardNumber>TGIF161492</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:50:23.793</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197386</sequenceNumber>
    <cardNumber>8888416116772424</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197387</sequenceNumber>
    <cardNumber>8888165471613474</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197388</sequenceNumber>
    <cardNumber>8888637311935325</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197389</sequenceNumber>
    <cardNumber>8888743743267463</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197390</sequenceNumber>
    <cardNumber>8888371639649414</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197391</sequenceNumber>
    <cardNumber>8888966715396939</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197392</sequenceNumber>
    <cardNumber>8888326257227966</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197393</sequenceNumber>
    <cardNumber>8888955417956141</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197394</sequenceNumber>
    <cardNumber>8888736444997247</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
  <uploadGVreq>
    <sequenceNumber>2197395</sequenceNumber>
    <cardNumber>8888293939213716</cardNumber>
    <reqtype>1</reqtype>
    <MopId>15</MopId>
    <amount>500.00</amount>
    <flag>1</flag>
    <expdate>2017-05-30T00:00:00</expdate>
    <UploadDate>2016-11-30T17:52:01.617</UploadDate>
    <companycode>VG</companycode>
    <UploadType></UploadType>
    <UploadDetail></UploadDetail>
    <UploadedBy></UploadedBy>
    <StoreNo></StoreNo>
    <PIN></PIN>
    <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
  </uploadGVreq>
 
</list_input>
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
try..

DECLARE @pawanxml XML
;WITH XMLNAMESPACES ('http://www.w3.org/TR/html4/' as h)
SELECT @pawanxml = 
(
	SELECT 
	(
		select voucherid as 'sequenceNumber',VOUCHERNO as 'cardNumber',
		'1' as 'reqtype','15' as 'MopId',
		REDEMPTIONFACEVALUE as 'amount','1' as 'flag',  
		VOUCHEREXPIRYDATE as 'expdate',
		blastdate as 'UploadDate','VG' as 'companycode', '' 'UploadType',
		'' 'UploadDetail','' 'UploadedBy',
		'' 'StoreNo','' 'PIN','5111a4c9-5007-4367-a4a1-071a2cfa1b26' GUID
		from vgt_voucher a, vgm_product b 
		where a.PRODUCTID=b.PRODUCTID
		and a.MERCHANTID=8      
		and BLASTDATE>='11/30/2016 00:00:00'
		and BLASTDATE<='11/30/2016 23:59:59'
		FOR XML PATH ('uploadGVreq'), TYPE, ROOT ('list_input') 
	)FOR XML PATH ('gvupload')
)
SELECT @pawanxml

Open in new window

Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

GUID element is still coming within <uploadGVreq></uploadGVreq>. It should show in the last before list_input

<gvupload xmlns:h="http://www.w3.org/TR/html4/">
  <list_input xmlns:h="http://www.w3.org/TR/html4/">
 <uploadGVreq>
      <sequenceNumber>1874098</sequenceNumber>
      <cardNumber>GmoTES1exUpljIwUBhYIxg==</cardNumber>
      <reqtype>1</reqtype>
      <MopId>15</MopId>
      <amount>500.00</amount>
      <flag>1</flag>
      <expdate>2017-05-30T00:00:00</expdate>
      <UploadDate>2016-11-30T17:50:23.793</UploadDate>
      <companycode>VG</companycode>
      <UploadType />
      <UploadDetail />
      <UploadedBy />
      <StoreNo />
      <PIN />
    </uploadGVreq>
 <uploadGVreq>
      <sequenceNumber>1874098</sequenceNumber>
      <cardNumber>GmoTES1exUpljIwUBhYIxg==</cardNumber>
      <reqtype>1</reqtype>
      <MopId>15</MopId>
      <amount>500.00</amount>
      <flag>1</flag>
      <expdate>2017-05-30T00:00:00</expdate>
      <UploadDate>2016-11-30T17:50:23.793</UploadDate>
      <companycode>VG</companycode>
      <UploadType />
      <UploadDetail />
      <UploadedBy />
      <StoreNo />
      <PIN />
    </uploadGVreq>
      <GUID>5111a4c9-5007-4367-a4a1-071a2cfa1b26</GUID>
</list_input>
</gvupload>
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
Try..

DECLARE @pawanxml XML
;WITH XMLNAMESPACES ('http://www.w3.org/TR/html4/' as h)
SELECT @pawanxml = 
(
	SELECT 
	(
		select voucherid as 'sequenceNumber',VOUCHERNO as 'cardNumber',
		'1' as 'reqtype','15' as 'MopId',
		REDEMPTIONFACEVALUE as 'amount','1' as 'flag',  
		VOUCHEREXPIRYDATE as 'expdate',
		blastdate as 'UploadDate','VG' as 'companycode', '' 'UploadType',
		'' 'UploadDetail','' 'UploadedBy',
		'' 'StoreNo','' 'PIN'
		from vgt_voucher a, vgm_product b 
		where a.PRODUCTID=b.PRODUCTID
		and a.MERCHANTID=8      
		and BLASTDATE>='11/30/2016 00:00:00'
		and BLASTDATE<='11/30/2016 23:59:59'
		FOR XML PATH ('uploadGVreq'), TYPE, ROOT ('list_input') 
	)FOR XML PATH ('gvupload')
)
set @pawanxml.modify('  insert  <GUID>"5111a4c9-5007-4367-a4a1-071a2cfa1b26"</GUID>  as last into (/gvupload)[1] ');  
SELECT @pawanxml

Open in new window

Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

This is perfectly fine.
How can i change the blank xml elements like

<UploadedBy />
to
<UploadedBy></UploadedBy/>
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
Try..

DECLARE @pawanxml XML
;WITH XMLNAMESPACES ('http://www.w3.org/TR/html4/' as h)
SELECT @pawanxml = 
(
	SELECT 
	(
		select voucherid as 'sequenceNumber',VOUCHERNO as 'cardNumber',
		'1' as 'reqtype','15' as 'MopId',
		REDEMPTIONFACEVALUE as 'amount','1' as 'flag',  
		VOUCHEREXPIRYDATE as 'expdate',
		blastdate as 'UploadDate','VG' as 'companycode', '' 'UploadType',
		'' 'UploadDetail','' 'UploadedBy',
		'' 'StoreNo','' 'PIN'
		from vgt_voucher a, vgm_product b 
		where a.PRODUCTID=b.PRODUCTID
		and a.MERCHANTID=8      
		and BLASTDATE>='11/30/2016 00:00:00'
		and BLASTDATE<='11/30/2016 23:59:59'
		FOR XML PATH ('uploadGVreq'), TYPE, ROOT ('list_input') 
	)FOR XML PATH ('gvupload')
)
set @pawanxml = replace(cast(@pawanxml as nvarchar(max)), '<UploadedBy />', '<UploadedBy></UploadedBy/>')
set @pawanxml.modify('  insert  <GUID>"5111a4c9-5007-4367-a4a1-071a2cfa1b26"</GUID>  as last into (/gvupload)[1] ');  
SELECT @pawanxml

Open in new window

Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

Hi Pawan,

Its not replacing the tag
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
Space issue.. try this

DECLARE @pawanxml XML
;WITH XMLNAMESPACES ('http://www.w3.org/TR/html4/' as h)
SELECT @pawanxml = 
(
	SELECT 
	(
		select voucherid as 'sequenceNumber',VOUCHERNO as 'cardNumber',
		'1' as 'reqtype','15' as 'MopId',
		REDEMPTIONFACEVALUE as 'amount','1' as 'flag',  
		VOUCHEREXPIRYDATE as 'expdate',
		blastdate as 'UploadDate','VG' as 'companycode', '' 'UploadType',
		'' 'UploadDetail','' 'UploadedBy',
		'' 'StoreNo','' 'PIN'
		from vgt_voucher a, vgm_product b 
		where a.PRODUCTID=b.PRODUCTID
		and a.MERCHANTID=8      
		and BLASTDATE>='11/30/2016 00:00:00'
		and BLASTDATE<='11/30/2016 23:59:59'
		FOR XML PATH ('uploadGVreq'), TYPE, ROOT ('list_input') 
	)FOR XML PATH ('gvupload')
)
set @pawanxml = replace(cast(@pawanxml as nvarchar(max)), '<UploadedBy/>', '<UploadedBy></UploadedBy/>')
set @pawanxml.modify('  insert  <GUID>"5111a4c9-5007-4367-a4a1-071a2cfa1b26"</GUID>  as last into (/gvupload)[1] ');  
SELECT @pawanxml

Open in new window

Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

Nope its not working
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
Try this... changed '' to ' '

DECLARE @pawanxml XML
;WITH XMLNAMESPACES ('http://www.w3.org/TR/html4/' as h)
SELECT @pawanxml = 
(
	SELECT 
	(
		select voucherid as 'sequenceNumber',VOUCHERNO as 'cardNumber',
		'1' as 'reqtype','15' as 'MopId',
		REDEMPTIONFACEVALUE as 'amount','1' as 'flag',  
		VOUCHEREXPIRYDATE as 'expdate',
		blastdate as 'UploadDate','VG' as 'companycode', ' ' 'UploadType',
		' ' 'UploadDetail',' ' 'UploadedBy',
		' ' 'StoreNo',' ' 'PIN'
		from vgt_voucher a, vgm_product b 
		where a.PRODUCTID=b.PRODUCTID
		and a.MERCHANTID=8      
		and BLASTDATE>='11/30/2016 00:00:00'
		and BLASTDATE<='11/30/2016 23:59:59'
		FOR XML PATH ('uploadGVreq'), TYPE, ROOT ('list_input') 
	)FOR XML PATH ('gvupload')
)
set @pawanxml.modify('  insert  <GUID>"5111a4c9-5007-4367-a4a1-071a2cfa1b26"</GUID>  as last into (/gvupload)[1] ');  
SELECT @pawanxml

Open in new window

Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

but then there is a space in between the tags and that will be treated as value
<StoreNo> </StoreNo>

Secondly, how can i remove the tag xmlns:h="http://www.w3.org/TR/html4/" from list_input

Why i am getting 2016-11-30T17:50:23.793 , T as extra charater in XML. I am retrieving Data value from Database.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
What format of date you want ?

eg for this - 2016-11-30T17:50:23.793 ?
Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

YYYY-MM-DD HH:MM:SS
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
Try this for date..

DECLARE @pawanxml XML
;WITH XMLNAMESPACES ('http://www.w3.org/TR/html4/' as h)
SELECT @pawanxml = 
(
	SELECT 
	(
		select voucherid as 'sequenceNumber',VOUCHERNO as 'cardNumber',
		'1' as 'reqtype','15' as 'MopId',
		REDEMPTIONFACEVALUE as 'amount','1' as 'flag',  
		FORMAT( CAST(VOUCHEREXPIRYDATE AS DATETIME) ,  'yyyy-MM-dd hh:mm:ss' ) as 'expdate',
		FORMAT( CAST(REDEMPTIONFACEVALUE AS DATETIME) ,  'yyyy-MM-dd hh:mm:ss' ) as 'UploadDate','VG' as 'companycode', ' ' 'UploadType',
		' ' 'UploadDetail',' ' 'UploadedBy',
		' ' 'StoreNo',' ' 'PIN'
		from vgt_voucher a, vgm_product b 
		where a.PRODUCTID=b.PRODUCTID
		and a.MERCHANTID=8      
		and BLASTDATE>='11/30/2016 00:00:00'
		and BLASTDATE<='11/30/2016 23:59:59'
		FOR XML PATH ('uploadGVreq'), TYPE, ROOT ('list_input') 
	)FOR XML PATH ('gvupload')
)
set @pawanxml.modify('  insert  <GUID>"5111a4c9-5007-4367-a4a1-071a2cfa1b26"</GUID>  as last into (/gvupload)[1] ');  
SELECT @pawanxml

Open in new window

Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

How can i remove the tag xmlns:h="http://www.w3.org/TR/html4/" from list_input
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

Thanks a lot. It working fine.
Avatar of Vouchagram India
Vouchagram India
Flag of India image

ASKER

Thanks a lot buddy. You have saved my efforts