BizTalk - SQL XQuery Script For Reading Party Data I

It is a pain trying to find out the configuration of a party in BizTalk.  Here is a script (using node methog) to read and decipher from an exported global binding file:

DECLARE @GlobalParty VARCHAR(MAX);

SELECT @GlobalParty = BulkColumn FROM OPENROWSET(BULK 'C:\Temp\PROD.GlobalParties.Bindings.xml', SINGLE_CLOB) AS a

SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns="http://schemas.datacontract.org/2004/07/Microsoft.BizTalk.Deployment.B2B.PartnerManagement"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:i="http://www.w3.org/2001/XMLSchema-instance"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d3p1="http://schemas.datacontract.org/2004/07/Microsoft.BizTalk.B2B.PartnerManagement"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d3p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Data.Objects.DataClasses"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d5p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d5p1="http://schemas.datacontract.org/2004/07/Microsoft.BizTalk.B2B.PartnerManagement"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d5p1="http://schemas.datacontract.org/2004/07/System.Data.Objects.DataClasses"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d6p1="http://schemas.datacontract.org/2004/07/Microsoft.BizTalk.B2B.PartnerManagement"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d7p1="http://schemas.datacontract.org/2004/07/System.Data.Objects.DataClasses"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d8p1="http://schemas.datacontract.org/2004/07/System.Data.Objects.DataClasses"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, ' xmlns:d9p1="http://schemas.datacontract.org/2004/07/System.Data.Objects.DataClasses"', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, 'z:', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, 'i:', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, 'd3p1:', '')

--SELECT @GlobalParty = REPLACE(@GlobalParty, 'd5p1:', '')

SELECT @GlobalParty = REPLACE(@GlobalParty, 'd6p1:', '')

DECLARE @GlobalPartyXML AS XML = CONVERT(XML, @GlobalParty, 2)

SELECT

     PartnerA

    ,PartnerB

    ,[Enabled]

    ,Protocol

    ,AS2AB_SenderIdentity Sender

    ,AS2AB_ReceiverIdentity Receiver

    ,AS2AB_MessageSigned Signed

    ,AS2AB_MessageEncrypted Encrypted

    ,AS2AB_EncryptionAlgorithm EncryptAlg

    ,AS2AB_NeedMDN MDNReq

    ,AS2AB_AsyncMDN MDNAsync

    ,AS2AB_SignMDN MDNSigned

    ,AS2AB_SignAlgorithm MDNSignAlg

FROM (SELECT

      ISNULL(p.value('PartnerA[1]', 'NVARCHAR(50)'), '') PartnerA

     ,ISNULL(p.value('PartnerB[1]', 'NVARCHAR(50)'), '') PartnerB

     ,ISNULL(a.value('Enabled[1]', 'NVARCHAR(50)'), '') [Enabled]

     ,ISNULL(a.value('Protocol[1]', 'NVARCHAR(50)'), '') Protocol

    

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/SenderIdentity[1]/Value)[1]', 'NVARCHAR(50)'), '') AS2AB_SenderIdentity

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ReceiverIdentity[1]/Value)[1]', 'NVARCHAR(50)'), '') AS2AB_ReceiverIdentity

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/MessageSigned)[1]', 'NVARCHAR(50)'), '') AS2AB_MessageSigned    

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/MessageEncrypted)[1]', 'NVARCHAR(50)'), '') AS2AB_MessageEncrypted   

     ,CASE WHEN ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/EncryptionAlgorithm)[1]', 'NVARCHAR(50)'), '') = 0 THEN 'DES3' ELSE 'RC2' END AS2AB_EncryptionAlgorithm

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/NeedMDN)[1]', 'NVARCHAR(50)'), '') AS2AB_NeedMDN

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/SendMDNAsynchronously)[1]', 'NVARCHAR(50)'), '') AS2AB_AsyncMDN    

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/SignMDN)[1]', 'NVARCHAR(50)'), '') AS2AB_SignMDN

     ,CASE WHEN ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/MicHashingAlgorithm)[1]', 'NVARCHAR(50)'), '') = 1 THEN 'SHA1' ELSE 'MD5' END AS2AB_SignAlgorithm

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/DispositionNotificationTo)[1]', 'NVARCHAR(50)'), '') AS2AB_DispositionNotificationTo        

    

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/SenderIdentity[1]/Value)[1]', 'NVARCHAR(50)'), '') AS2BA_SenderIdentity

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ReceiverIdentity[1]/Value)[1]', 'NVARCHAR(50)'), '') AS2BA_ReceiverIdentity

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/MessageSigned)[1]', 'NVARCHAR(50)'), '') AS2BA_MessageSigned    

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/MessageEncrypted)[1]', 'NVARCHAR(50)'), '') AS2BA_MessageEncrypted   

     ,CASE WHEN ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/EncryptionAlgorithm)[1]', 'NVARCHAR(50)'), '') = 0 THEN 'DES3' ELSE 'RC2' END AS2BA_EncryptionAlgorithm

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/NeedMDN)[1]', 'NVARCHAR(50)'), '') AS2BA_NeedMDN

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/SendMDNAsynchronously)[1]', 'NVARCHAR(50)'), '') AS2BA_AsyncMDN    

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/SignMDN)[1]', 'NVARCHAR(50)'), '') AS2BA_SignMDN

     ,CASE WHEN ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/MicHashingAlgorithm)[1]', 'NVARCHAR(50)'), '') = 1 THEN 'SHA1' ELSE 'MD5' END AS2BA_SignAlgorithm

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/DispositionNotificationTo)[1]', 'NVARCHAR(50)'), '') AS2BA_DispositionNotificationTo

FROM @GlobalPartyXML.nodes('//Partnership') Partnership(p)

CROSS APPLY Partnership.p.nodes('Agreements/Agreement') Agreement(a)) t

WHERE t.Protocol = 'as2'

SELECT

     PartnerA

    ,PartnerB

    ,[Enabled]

    ,Protocol

    ,X12AB_DestinationPartyName DstPartyName

    ,X12AB_SenderQualifier ISA5

    ,X12AB_SenderId ISA6

    ,X12AB_ReceiverQualifier ISA7

    ,X12AB_ReceiverId ISA8

    ,X12AB_ComponentSeparator ComponentSep

    ,X12AB_ElementSeparator ElementSep

    ,X12AB_SegmentTerminator SegTerminator

    ,X12AB_MessageId FuncGroup   

    ,X12AB_SenderApplicationId GS2

    ,X12AB_ReceiverApplicationId GS3  

    ,X12AB_HeaderVersion GS8

   

    ,X12BA_DestinationPartyName DstPartyName

    ,X12BA_SenderQualifier ISA5

    ,X12BA_SenderId ISA6

    ,X12BA_ReceiverQualifier ISA7

    ,X12BA_ReceiverId ISA8

    ,X12BA_ComponentSeparator ComponentSep

    ,X12BA_ElementSeparator ElementSep

    ,X12BA_SegmentTerminator SegTerminator

    ,X12BA_MessageId FuncGroup   

    ,X12BA_SenderApplicationId GS2

    ,X12BA_ReceiverApplicationId GS3

    ,X12BA_HeaderVersion GS8    

FROM (SELECT

      ISNULL(a.value('../../PartnerA[1]', 'NVARCHAR(50)'), '') PartnerA

     ,ISNULL(a.value('../../PartnerB[1]', 'NVARCHAR(50)'), '') PartnerB

     ,ISNULL(a.value('Enabled[1]', 'NVARCHAR(50)'), '') [Enabled]

     ,ISNULL(a.value('Protocol[1]', 'NVARCHAR(50)'), '') Protocol

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/AgreementAliases[1]/AgreementAlias[1]/Value)[1]', 'NVARCHAR(50)'), '') X12AB_DestinationPartyName

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/SenderIdentity[1]/Qualifier)[1]', 'NVARCHAR(50)'), '') X12AB_SenderQualifier

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/SenderIdentity[1]/Value)[1]', 'NVARCHAR(50)'), '') X12AB_SenderId         

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ReceiverIdentity[1]/Qualifier)[1]', 'NVARCHAR(50)'), '') X12AB_ReceiverQualifier

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ReceiverIdentity[1]/Value)[1]', 'NVARCHAR(50)'), '') X12AB_ReceiverId    

     ,CHAR(ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/ComponentSeparator)[1]', 'NVARCHAR(50)'), 32)) X12AB_ComponentSeparator

     ,CHAR(ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/DataElementSeparator)[1]', 'NVARCHAR(50)'), 32)) X12AB_ElementSeparator

     ,CHAR(ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/SegmentTerminator)[1]', 'NVARCHAR(50)'), 32)) X12AB_SegmentTerminator

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/EnvelopeOverrides[1]/X12EnvelopeOverrides[1]/MessageId)[1]', 'NVARCHAR(50)'), '') X12AB_MessageId

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/EnvelopeOverrides[1]/X12EnvelopeOverrides[1]/SenderApplicationId)[1]', 'NVARCHAR(50)'), '') X12AB_SenderApplicationId

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/EnvelopeOverrides[1]/X12EnvelopeOverrides[1]/ReceiverApplicationId)[1]', 'NVARCHAR(50)'), '') X12AB_ReceiverApplicationId

     ,ISNULL(a.value('(OnewayAgreementAtoB[1]/ProtocolSettings[1]/EnvelopeOverrides[1]/X12EnvelopeOverrides[1]/HeaderVersion)[1]', 'NVARCHAR(50)'), '') X12AB_HeaderVersion

    

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/AgreementAliases[1]/AgreementAlias[1]/Value)[1]', 'NVARCHAR(50)'), '') X12BA_DestinationPartyName

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/SenderIdentity[1]/Qualifier)[1]', 'NVARCHAR(50)'), '') X12BA_SenderQualifier

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/SenderIdentity[1]/Value)[1]', 'NVARCHAR(50)'), '') X12BA_SenderId         

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ReceiverIdentity[1]/Qualifier)[1]', 'NVARCHAR(50)'), '') X12BA_ReceiverQualifier

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ReceiverIdentity[1]/Value)[1]', 'NVARCHAR(50)'), '') X12BA_ReceiverId         

     ,CHAR(ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/ComponentSeparator)[1]', 'NVARCHAR(50)'), 32)) X12BA_ComponentSeparator

     ,CHAR(ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/DataElementSeparator)[1]', 'NVARCHAR(50)'), 32)) X12BA_ElementSeparator

     ,CHAR(ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/SegmentTerminator)[1]', 'NVARCHAR(50)'), 32)) X12BA_SegmentTerminator

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/EnvelopeOverrides[1]/X12EnvelopeOverrides[1]/MessageId)[1]', 'NVARCHAR(50)'), '') X12BA_MessageId

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/EnvelopeOverrides[1]/X12EnvelopeOverrides[1]/SenderApplicationId)[1]', 'NVARCHAR(50)'), '') X12BA_SenderApplicationId

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/EnvelopeOverrides[1]/X12EnvelopeOverrides[1]/ReceiverApplicationId)[1]', 'NVARCHAR(50)'), '') X12BA_ReceiverApplicationId

     ,ISNULL(a.value('(OnewayAgreementBtoA[1]/ProtocolSettings[1]/EnvelopeOverrides[1]/X12EnvelopeOverrides[1]/HeaderVersion)[1]', 'NVARCHAR(50)'), '') X12BA_HeaderVersion

FROM @GlobalPartyXML.nodes('//Partnership/Agreements/Agreement') Agreement(a)) t

WHERE t.Protocol = 'x12'