BizTalk - SQL XQuery Script For Reading Party Data II

It is a pain trying to find out the configuration of a party in BizTalk.  Here is a script (using OPENXML) 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 INT

EXEC sp_xml_preparedocument @GlobalPartyXML OUTPUT, @GlobalParty

SELECT

       PartnerA

      ,PartnerB

      ,[Enabled]

      ,Protocol

      ,AS2AB_SenderIdentity

      ,AS2AB_ReceiverIdentity

      ,AS2AB_MessageSigned

      ,AS2AB_MessageEncrypted

      ,CASE WHEN AS2AB_EncryptionAlgorithm = '0' THEN 'DES3' ELSE 'RC2' END AS2AB_EncryptionAlgorithm

      ,AS2AB_NeedMDN

      ,AS2AB_AsyncMDN

      ,AS2AB_SignMDN

      ,CASE WHEN AS2AB_SignAlgorithm = '1' THEN 'SHA1' ELSE 'MD5' END AS2AB_SignAlgorithm

      ,AS2AB_DispositionNotificationTo

      ,AS2BA_SenderIdentity

      ,AS2BA_ReceiverIdentity

      ,AS2BA_MessageSigned

      ,AS2BA_MessageEncrypted

      ,CASE WHEN AS2BA_EncryptionAlgorithm = '0' THEN 'DES3' ELSE 'RC2' END AS2BA_EncryptionAlgorithm

      ,AS2BA_NeedMDN

      ,AS2BA_AsyncMDN

      ,AS2BA_SignMDN

      ,CASE WHEN AS2BA_SignAlgorithm = '1' THEN 'SHA1' ELSE 'MD5' END AS2BA_SignAlgorithm

      ,AS2BA_DispositionNotificationTo

FROM (SELECT *

      FROM OPENXML(@GlobalPartyXML, '//Partnership/Agreements/Agreement', 1)

        WITH (

                  PartnerA                                  VARCHAR(50) '../../PartnerA',

                  PartnerB                                  VARCHAR(50) '../../PartnerB',

                  [Enabled]                                 VARCHAR(10) 'Enabled',

                  Protocol                                  VARCHAR(10) 'Protocol',

                 

                  AS2AB_SenderIdentity                VARCHAR(50)      'OnewayAgreementAtoB/SenderIdentity/Value',

                  AS2AB_ReceiverIdentity              VARCHAR(50)      'OnewayAgreementAtoB/ReceiverIdentity/Value',

                  AS2AB_MessageSigned                       VARCHAR(10)      'OnewayAgreementAtoB/ProtocolSettings/MessageSigned',

                  AS2AB_MessageEncrypted              VARCHAR(10)      'OnewayAgreementAtoB/ProtocolSettings/MessageEncrypted',

                  AS2AB_EncryptionAlgorithm           VARCHAR(10)      'OnewayAgreementAtoB/ProtocolSettings/EncryptionAlgorithm',

                  AS2AB_NeedMDN                             VARCHAR(10)      'OnewayAgreementAtoB/ProtocolSettings/NeedMDN',

                  AS2AB_AsyncMDN                            VARCHAR(10)      'OnewayAgreementAtoB/ProtocolSettings/SendMDNAsynchronously',

                  AS2AB_SignMDN                             VARCHAR(10)      'OnewayAgreementAtoB/ProtocolSettings/SignMDN',

                  AS2AB_SignAlgorithm                       VARCHAR(10)      'OnewayAgreementAtoB/ProtocolSettings/MicHashingAlgorithm',

                  AS2AB_DispositionNotificationTo     VARCHAR(90)      'OnewayAgreementAtoB/ProtocolSettings/DispositionNotificationTo',

                 

                  AS2BA_SenderIdentity                VARCHAR(50)      'OnewayAgreementBtoA/SenderIdentity/Value',

                  AS2BA_ReceiverIdentity              VARCHAR(50)      'OnewayAgreementBtoA/ReceiverIdentity/Value',

                  AS2BA_MessageSigned                       VARCHAR(10)      'OnewayAgreementBtoA/ProtocolSettings/MessageSigned',

                  AS2BA_MessageEncrypted              VARCHAR(10)      'OnewayAgreementBtoA/ProtocolSettings/MessageEncrypted',

                  AS2BA_EncryptionAlgorithm           VARCHAR(10)      'OnewayAgreementBtoA/ProtocolSettings/EncryptionAlgorithm',

                  AS2BA_NeedMDN                             VARCHAR(10)      'OnewayAgreementBtoA/ProtocolSettings/NeedMDN',

                  AS2BA_AsyncMDN                            VARCHAR(10)      'OnewayAgreementBtoA/ProtocolSettings/SendMDNAsynchronously',

                  AS2BA_SignMDN                             VARCHAR(10)      'OnewayAgreementBtoA/ProtocolSettings/SignMDN',

                  AS2BA_SignAlgorithm                       VARCHAR(10)      'OnewayAgreementBtoA/ProtocolSettings/MicHashingAlgorithm',

                  AS2BA_DispositionNotificationTo     VARCHAR(90)      'OnewayAgreementBtoA/ProtocolSettings/DispositionNotificationTo'

                  )) 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 *

      FROM OPENXML(@GlobalPartyXML, '//Partnership/Agreements/Agreement', 1)

        WITH (PartnerA                                VARCHAR(50) '../../PartnerA',

                  PartnerB                                  VARCHAR(50) '../../PartnerB',

                  [Enabled]                                 VARCHAR(10) 'Enabled',

                  Protocol                                  VARCHAR(10) 'Protocol',

                 

                  X12AB_DestinationPartyName          VARCHAR(50)      'OnewayAgreementAtoB/AgreementAliases/AgreementAlias/Value',

                  X12AB_SenderQualifier               VARCHAR(50)      'OnewayAgreementAtoB/SenderIdentity/Qualifier',

                  X12AB_SenderId                            VARCHAR(50)      'OnewayAgreementAtoB/SenderIdentity/Value',

                  X12AB_ReceiverQualifier             VARCHAR(50)      'OnewayAgreementAtoB/ReceiverIdentity/Qualifier',

                  X12AB_ReceiverId                    VARCHAR(50)      'OnewayAgreementAtoB/ReceiverIdentity/Value',

                  X12AB_ComponentSeparator            VARCHAR(50)      'OnewayAgreementAtoB/ProtocolSettings/ComponentSeparator',             

                  X12AB_ElementSeparator              VARCHAR(50)      'OnewayAgreementAtoB/ProtocolSettings/DataElementSeparator',

                  X12AB_SegmentTerminator             VARCHAR(50)      'OnewayAgreementAtoB/ProtocolSettings/SegmentTerminator',

                  X12AB_MessageId                           VARCHAR(50) 'OnewayAgreementAtoB/ProtocolSettings/EnvelopeOverrides/X12EnvelopeOverrides/MessageId',

                  X12AB_SenderApplicationId           VARCHAR(50) 'OnewayAgreementAtoB/ProtocolSettings/EnvelopeOverrides/X12EnvelopeOverrides/SenderApplicationId',

                  X12AB_ReceiverApplicationId         VARCHAR(50) 'OnewayAgreementAtoB/ProtocolSettings/EnvelopeOverrides/X12EnvelopeOverrides/ReceiverApplicationId',

                  X12AB_HeaderVersion                       VARCHAR(50) 'OnewayAgreementAtoB/ProtocolSettings/EnvelopeOverrides/X12EnvelopeOverrides/HeaderVersion',

                 

                  X12BA_DestinationPartyName          VARCHAR(50)      'OnewayAgreementBtoA/AgreementAliases/AgreementAlias/Value',

                  X12BA_SenderQualifier               VARCHAR(50)      'OnewayAgreementBtoA/SenderIdentity/Qualifier',

                  X12BA_SenderId                            VARCHAR(50)      'OnewayAgreementBtoA/SenderIdentity/Value',

                  X12BA_ReceiverQualifier             VARCHAR(50)      'OnewayAgreementBtoA/ReceiverIdentity/Qualifier',

                  X12BA_ReceiverId                    VARCHAR(50)      'OnewayAgreementBtoA/ReceiverIdentity/Value',

                  X12BA_ComponentSeparator            VARCHAR(50)      'OnewayAgreementBtoA/ProtocolSettings/ComponentSeparator',             

                  X12BA_ElementSeparator              VARCHAR(50)      'OnewayAgreementBtoA/ProtocolSettings/DataElementSeparator',

                  X12BA_SegmentTerminator             VARCHAR(50)      'OnewayAgreementBtoA/ProtocolSettings/SegmentTerminator',

                  X12BA_MessageId                           VARCHAR(50) 'OnewayAgreementBtoA/ProtocolSettings/EnvelopeOverrides/X12EnvelopeOverrides/MessageId',

                  X12BA_SenderApplicationId           VARCHAR(50) 'OnewayAgreementBtoA/ProtocolSettings/EnvelopeOverrides/X12EnvelopeOverrides/SenderApplicationId',

                  X12BA_ReceiverApplicationId         VARCHAR(50) 'OnewayAgreementBtoA/ProtocolSettings/EnvelopeOverrides/X12EnvelopeOverrides/ReceiverApplicationId',

                  X12BA_HeaderVersion                       VARCHAR(50) 'OnewayAgreementBtoA/ProtocolSettings/EnvelopeOverrides/X12EnvelopeOverrides/HeaderVersion'                 

                  )) t

WHERE t.Protocol = 'x12'

     --,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

EXEC sp_xml_removedocument @GlobalPartyXML;