MY XML is not working

• niladri.primalink

  SSCommitted

  Points: 1751

  More actions

  October 18, 2013 at 2:49 am

  #284724

  I have written a sql syntax to create an XML. Here is the code

  Code:

  WITH XMLNAMESPACES ( 'urn:swift:saa:xsd:saa.2.0' AS Saa)

  select

  '2.0.2' 'Saa:Revision',

  DbtrAcct_Id_IBAN 'Saa:Header/Saa:Message/Saa:SenderReference',

  1 'Saa:Header/Saa:Message/Saa:MessageIdentifier',

  'camt.056.001.01' 'Saa:Header/Saa:Message/saa:Format',

  'cn=mx01,0='+DbtrAgt_FinInstnId_BIC+'o=swift' 'Saa:Header/Saa:Message/Saa:Sender/Saa:DN' ,

  'cn=mx99,0='+CdtrAgt_FinInstnId_BIC+'o=swift' 'Saa:Header/Saa:Message/Saa:Receiver/Saa:DN',

  'swift.generic.fast!p' 'Saa:Header/Saa:Message/Saa:NetworkInfo/Saa:Service',

  'Payment File from Bank BANKAABB' 'Saa:Header/Saa:Message/Saa:NetworkInfo/Saa:SWIFT:NetNetworkInfo/Saa:FileDescription',

  'Sent on 01/04/2009' 'Saa:Header/Saa:Message/Saa:NetworkInfo/Saa:SWIFT:NetNetworkInfo/Saa:FileInfo',

  'SHA=256' 'Saa:Header/Saa:Message/Saa:SecurityInfo/Saa:SWIFTNetSecurityInfo/Saa:SWIFTNetSecurityInfo',

  'pnRSc38nofZ3AF1pmWSnmoLpfprVb/pJXx5l+PhJC68=' 'Saa:Header/Saa:Message/Saa:SecurityInfo/Saa:SWIFTNetSecurityInfo/Saa:FileDigestValue',

  filename 'Saa:Header/Saa:Message/Saa:FileLogicalName',

  cast(dbo.FIM_GetXMLCamt056UDF(2,1) as xml) 'Saa:Body'

  from FimBank_TransDetails ftd where ftd.AutoId=2

  for XML path (''),

  root ('Saa:DataPDU'), ELEMENTS XSINIL

  I got an error "XML name space prefix 'saa' declaration is missing for FOR XML column name 'Saa:Header/Saa:Message/saa:Format'."

  Searched in google but no satisfactory answer I am getting.

  However I want to see the output like below

  <Saa:DataPDU mins:Saa="urn:swift:saa:xsd:saa.2.0">

  <Saa:Revision>2.0.2</Saa:Revision>

  <Saa:Header>

  <Saa:Message>

  <Saa:SenderReference>File1</Saa:SenderReference>

  <Saa:MessageIdentifier>Req001</Saa:MessageIdentifier>

  <saa:Format>File</Saa:Format>

  <Saa:Sender>

  <Saa:DN>cn=mx01,0=bankaabb,o=swift</Saa:DN>

  </Saa:Sender>

  <Saa:Receiver>

  <Saa:DN>cn=mx99,0=bankyyzz,o=swift</Saa:DN>

  </Saa:Receiver>

  <Saa:NetworkInfo>

  <Saa:Service>Service.FileAct</Saa:Service>

  <Saa:SWIFT:NetNetworkInfo>

  <Saa:FileDescription>Payment File from Bank BANKAABB</Saa:FileDescription>

  <Saa:FileInfo>Sent on 01/04/2009</Saa:FileInfo>

  </Saa:SWIFT:NetNetworkInfo>

  </Saa:NetworkInfo>

  <Saa:SecurityInfo>

  <Saa:SWIFTNetSecurityInfo>

  <Saa:FileDigestAlgorithm>SHA=256</Saa:FileDigestAlgorithm>

  <Saa:FileDigestValue>pnRSc38nofZ3AF1pmWSnmoLpfprVb/pJXx5l+PhJC68=</Saa:FileDigestValue>

  </Saa:SWIFTNetSecurityInfo>

  </Saa:SecurityInfo>

  <Saa:FileLogicalName>Payments-Bank BANKAABB</Saa:FileLogicalName>

  </Saa:Message>

  </Saa:Header>

  <Saa:Body>payments,fct</Saa:Body>

  </Saa:DataPDU>

  Could you please guide me? Please help

  Thanks in advance!!

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  October 18, 2013 at 3:25 am

  #1659614

  XML is generally case-sensitive so try changing

  'Saa:Header/Saa:Message/saa:Format'."

  to

  'Saa:Header/Saa:Message/Saa:Format'."

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• niladri.primalink

  SSCommitted

  Points: 1751

  More actions

  October 18, 2013 at 3:29 am

  #1659615

  Thanks for your reply

  But I am now getting error

  Column name 'Saa:Header/Saa:Message/Saa:NetworkInfo/Saa:SWIFT:NetNetworkInfo/Saa:FileDescription' contains an invalid XML identifier as required by FOR XML; ':'(0x003A) is the first character at fault.

  Please help!

• niladri.primalink

  SSCommitted

  Points: 1751

  More actions

  October 18, 2013 at 3:53 am

  #1659620

  Hi

  it is now working

  DECLARE @xmlDoc XML

  WITH XMLNAMESPACES ('urn:swift:saa:xsd:saa.2.0' AS Saa)

  SELECT @xmlDoc =(select

  '2.0.2' 'Saa:Revision',

  DbtrAcct_Id_IBAN 'Saa:Header/Saa:Message/Saa:SenderReference',

  1 'Saa:Header/Saa:Message/Saa:MessageIdentifier',

  'camt.056.001.01' 'Saa:Header/Saa:Message/Saa:Format',

  'cn=mx01,0='+DbtrAgt_FinInstnId_BIC+'o=swift' 'Saa:Header/Saa:Message/Saa:Sender/Saa:DN' ,

  'cn=mx99,0='+CdtrAgt_FinInstnId_BIC+'o=swift' 'Saa:Header/Saa:Message/Saa:Receiver/Saa:DN',

  'swift.generic.fast!p' 'Saa:Header/Saa:Message/Saa:NetworkInfo/Saa:Service',

  -- 'Payment File from Bank BANKAABB' 'Saa:Header/Saa:Message/Saa:NetworkInfo/Saa:SWIFT:NetNetworkInfo/Saa:FileDescription',

  -- 'Sent on 01/04/2009' 'Saa:Header/Saa:Message/Saa:NetworkInfo/Saa:SWIFT:NetNetworkInfo/Saa:FileInfo',

  'SHA=256' 'Saa:Header/Saa:Message/Saa:SecurityInfo/Saa:SWIFTNetSecurityInfo/Saa:SWIFTNetSecurityInfo',

  'pnRSc38nofZ3AF1pmWSnmoLpfprVb/pJXx5l+PhJC68=' 'Saa:Header/Saa:Message/Saa:SecurityInfo/Saa:SWIFTNetSecurityInfo/Saa:FileDigestValue',

  filename 'Saa:Header/Saa:Message/Saa:FileLogicalName',

  cast(dbo.FIM_GetXMLCamt056UDF(64,1) as xml) 'Saa:Body'

  from FimBank_TransDetails ftd where ftd.AutoId=64

  for XML path ('DataPDU'),

  root ('Saa:DataPDU'))

  Select @xmlDoc

  but i don't want to see the XML path. Is there any way to do that?

  Thanks in advance!

• niladri.primalink

  SSCommitted

  Points: 1751

  More actions

  October 18, 2013 at 4:05 am

  #1659626

  Thanks

  It is now working. Only a small 's' was making my life in danger. Thanks buddy.