Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MY XML is not working Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 2:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:33 AM
Points: 95, Visits: 170
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!!
Post #1506026
Posted Friday, October 18, 2013 3:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
XML is generally case-sensitive so try changing

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


to

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


____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1506046
Posted Friday, October 18, 2013 3:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:33 AM
Points: 95, Visits: 170
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!
Post #1506047
Posted Friday, October 18, 2013 3:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:33 AM
Points: 95, Visits: 170
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!
Post #1506052
Posted Friday, October 18, 2013 4:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:33 AM
Points: 95, Visits: 170
Thanks

It is now working. Only a small 's' was making my life in danger. Thanks buddy.
Post #1506061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse