Manipulate XML SOAP OUTPUT to Tabular

Transact-SQL
1

Hi All,

I tried to manipulate the result unfortunately no result return. Can anyone help me on this please?

declare @xmldata XML = '<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Body>
        <getDevicesResponse xmlns="urn:com.macrovision:flexnet/opsembedded">
            <statusInfo>
                <status>SUCCESS</status>
                <reason>The following unsupported parameters were ignored : &quot;deviceResponseConfig/features&quot;, &quot;deviceResponseConfig/preBuiltLicense&quot;, &quot;deviceResponseConfig/preBuiltProduct&quot;</reason>
            </statusInfo>
            <responseData>
                <device>
                    <deviceIdentifier>
                        <deviceType>SERVER</deviceType>
                        <deviceId>2214-SIS-OPS01_AYBGMUDE</deviceId>
                        <serverIds>
                            <serverId>2214-SIS-OPS01_AYBGMUDE</serverId>
                            <serverId xsi:nil="true"/>
                        </serverIds>
                        <deviceIdType>PUBLISHER_DEFINED</deviceIdType>
                        <publisherName>XYZ Corp</publisherName>
                    </deviceIdentifier>
                    <alias>2214-SIS-OPS01_AYBGMUDE</alias>
                    <hostTypeName>
                        <name>FLX_SERVER</name>
                    </hostTypeName>
                    <deviceStatus>ACTIVE</deviceStatus>
                    <channelPartners>
                        <channelPartner>
                            <tierName>bo.constants.partnertiernames.endcustomer</tierName>
                            <organizationUnit>
                                <uniqueId>2865576</uniqueId>
                                <primaryKeys>
                                    <name>ZJ Co., Ltd. - 228177</name>
                                </primaryKeys>
                            </organizationUnit>
                        </channelPartner>
                    </channelPartners>
                    <soldTo>
                        <displayName>ZJ Co., Ltd. - 228177</displayName>
                        <id>228177</id>
                        <name>ZJ Co., Ltd. - 228177</name>
                    </soldTo>
                    <hasPrebuiltLicense>false</hasPrebuiltLicense>
                    <publisherIdName>
                        <name>XYZ Corp</name>
                    </publisherIdName>
                    <addonLineItemData>
                        <activationId>5CH0-SZYO-1F4WOH3D</activationId>
                        <enabledCount>1</enabledCount>
                        <consumedCount>1</consumedCount>
                        <licenseState>LICENSE_NOT_GENERATED</licenseState>
                        <entitledProducts>
                            <entitledProduct>
                                <product>
                                    <uniqueId>191675</uniqueId>
                                    <primaryKeys>
                                        <name>In 2017 Runtime Tag without I/O</name>
                                        <version>2017</version>
                                    </primaryKeys>
                                </product>
                                <quantity>1</quantity>
                            </entitledProduct>
                        </entitledProducts>
                    </addonLineItemData>
                    <machineType>UNKNOWN</machineType>
                </device>
                <device>
                    <deviceIdentifier>
                        <deviceType>SERVER</deviceType>
                        <deviceId>4120WPV_Q11WK85Z</deviceId>
                        <serverIds>
                            <serverId>4120WPV_Q11WK85Z</serverId>
                            <serverId xsi:nil="true"/>
                        </serverIds>
                        <deviceIdType>PUBLISHER_DEFINED</deviceIdType>
                        <publisherName>XYZ Corp</publisherName>
                    </deviceIdentifier>
                    <alias>4120WPV_Q11WK85Z</alias>
                    <hostTypeName>
                        <name>FLX_SERVER</name>
                    </hostTypeName>
                    <deviceStatus>ACTIVE</deviceStatus>
                    <channelPartners>
                        <channelPartner>
                            <tierName>bo.constants.partnertiernames.endcustomer</tierName>
                            <organizationUnit>
                                <uniqueId>2867868</uniqueId>
                                <primaryKeys>
                                    <name>TC - 253931</name>
                                </primaryKeys>
                            </organizationUnit>
                        </channelPartner>
                    </channelPartners>
                    <soldTo>
                        <displayName>TC - 253931</displayName>
                        <id>253931</id>
                        <name>TC - 253931</name>
                    </soldTo>
                    <hasPrebuiltLicense>false</hasPrebuiltLicense>
                    <publisherIdName>
                        <name>XYZ Corp</name>
                    </publisherIdName>
                    <addonLineItemData>
                        <activationId>DH8S-HV46-7DH4-6E3W</activationId>
                        <enabledCount>1</enabledCount>
                        <consumedCount>1</consumedCount>
                        <licenseState>LICENSE_NOT_GENERATED</licenseState>
                        <entitledProducts>
                            <entitledProduct>
                                <product>
                                    <uniqueId>191271</uniqueId>
                                    <primaryKeys>
                                        <name>OEM In 2017 RT ag with I/O</name>
                                        <version>2017</version>
                                    </primaryKeys>
                                </product>
                                <quantity>1</quantity>
                            </entitledProduct>
                        </entitledProducts>
                    </addonLineItemData>
                    <machineType>UNKNOWN</machineType>
                </device>
            </responseData>
        </getDevicesResponse>
    </soapenv:Body>
</soapenv:Envelope>
'

;WITH XMLNAMESPACES(
     'http://schemas.xmlsoap.org/soap/envelope/'     as soapenv
)
SELECT
  r.value('(deviceIdentifier/deviceType/text())[1]',     'varchar(100)') AS deviceType
  ,r.value('(deviceIdentifier/deviceId/text())[1]','varchar(100)') AS deviceId
  ,r.value('(soldTo/id/text())[1]','varchar(100)') AS soldTo
  ,r.value('(deviceIdentifier/deviceStatus/text())[1]','varchar(100)') AS deviceStatus
  ,r.value('(deviceIdentifier/channelPartners/channelPartner/organizationUnit/primaryKeys/Name/text())[1]','varchar(100)') AS channelPartners
FROM @xmldata.nodes('
    /soapenv:Envelope/soapenv:Body/getDevicesResponse/responseData/device
  ') AS t1(r);

Many thanks.

Regards,
Micheale

2

Thanks. I managed figured out the solution:

;WITH XMLNAMESPACES(
     'http://schemas.xmlsoap.org/soap/envelope/'     as soapenv
)
SELECT
  t1.r.value('(./*:deviceIdentifier/*:deviceType/text())[1]',     'varchar(100)') AS deviceType
  ,t1.r.value('(./*:deviceIdentifier/*:deviceId/text())[1]','varchar(100)') AS deviceId
  ,r.value('(./*:soldTo/*:id/text())[1]','varchar(100)') AS soldTo
  ,r.value('(./*:deviceStatus/text())[1]','varchar(100)') AS deviceStatus
  ,r.value('(./*:channelPartners/*:channelPartner/*:organizationUnit/*:primaryKeys/*:name/text())[1]','varchar(100)') AS channelPartners
FROM @xmldata.nodes('/*:Envelope/*:Body/*:getDevicesResponse/*:responseData/*:device') AS t1(r);

Regards,
Micheale

1 Like
3

Hi,

Sorry. 1 more question. If the package having mutiple Channel & Line Item Attributes. May I know how to handle it?

declare @xmldata XML = '<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Body>
        <searchEntitlementLineItemPropertiesResponse xmlns="urn:v5.webservices.operations.flexnet.com">
            <statusInfo>
                <status>SUCCESS</status>
            </statusInfo>
            <entitlementLineItem>
                <activationId>
                    <uniqueId>HID-37309295</uniqueId>
                    <primaryKeys>
                        <activationId>UUU7-75BA-BE51-IMYY</activationId>
                    </primaryKeys>
                </activationId>
                <state>DEPLOYED</state>
                <activatableItemType>LINEITEM</activatableItemType>
                <orderLineNumber>1</orderLineNumber>
                <entitlementId>
                    <uniqueId>HID-11909557</uniqueId>
                    <primaryKeys>
                        <entitlementId>1608208-A-278443</entitlementId>
                    </primaryKeys>
                </entitlementId>
                <soldTo>
                    <uniqueId>HID-3360879</uniqueId>
                    <primaryKeys>
                        <id>278443</id>
                    </primaryKeys>
                </soldTo>
                <soldToDisplayName>MICK - 278443</soldToDisplayName>
                <entitlementState>DEPLOYED</entitlementState>
                <allowPortalLogin>true</allowPortalLogin>
                <bulkEntSoldToDisplayName></bulkEntSoldToDisplayName>
                <product>
                    <uniqueId>HID-193048</uniqueId>
                    <primaryKeys>
                        <name>ATZ Development Studio 2020 Large 5K/3K/500</name>
                        <version>2020</version>
                    </primaryKeys>
                </product>
                <productDescription></productDescription>
                <partNumber>
                    <uniqueId>HID-128481</uniqueId>
                    <primaryKeys>
                        <partId>DevStd-03-N-20</partId>
                    </primaryKeys>
                </partNumber>
                <partNumberDescription>ATZ Development Studio 2020 Large 5K/3K/500</partNumberDescription>
                <licenseTechnology>
                    <uniqueId>HID-10700</uniqueId>
                    <primaryKeys>
                        <name>FlexNet Licensing</name>
                    </primaryKeys>
                </licenseTechnology>
                <licenseModel>
                    <uniqueId>HID-29993</uniqueId>
                    <primaryKeys>
                        <name>FNE License Model (V2)</name>
                    </primaryKeys>
                </licenseModel>
                <startDate>2022-03-24</startDate>
                <startDateOption>DEFINE_NOW</startDateOption>
                <isPermanent>true</isPermanent>
                <numberOfCopies>3</numberOfCopies>
                <fulfilledAmount>1</fulfilledAmount>
                <numberOfRemainingCopies>2</numberOfRemainingCopies>
                <isTrusted>false</isTrusted>
                <entitledProducts>
                    <entitledProduct>
                        <product>
                            <uniqueId>HID-193048</uniqueId>
                            <primaryKeys>
                                <name>ATZ Development Studio 2020 Large 5K/3K/500</name>
                                <version>2020</version>
                            </primaryKeys>
                        </product>
                        <quantity>1</quantity>
                    </entitledProduct>
                </entitledProducts>
                <channelPartners>
                    <channelPartner>
                        <tierName>bo.constants.partnertiernames.endcustomer</tierName>
                        <accountUnit>
                            <uniqueId>HID-3360879</uniqueId>
                            <primaryKeys>
                                <id>278443</id>
                            </primaryKeys>
                        </accountUnit>
                        <contact/>
                        <currentOwner>true</currentOwner>
                    </channelPartner>
                    <channelPartner>
                        <tierName>bo.constants.partnertiernames.tier1</tierName>
                        <accountUnit>
                            <uniqueId>HID-2864136</uniqueId>
                            <primaryKeys>
                                <id>254079</id>
                            </primaryKeys>
                        </accountUnit>
                        <contact/>
                        <currentOwner>false</currentOwner>
                    </channelPartner>
                </channelPartners>
                <createdOnDateTime>2022-03-25T18:47:44.360Z</createdOnDateTime>
                <lastModifiedDateTime>2022-07-25T02:02:40.537Z</lastModifiedDateTime>
                <createdBy>fXTR</createdBy>
                <lastModifiedBy>fXTR</lastModifiedBy>
                <lineItemAttributes>
                    <attribute>
                        <attributeName>EndUserName</attributeName>
                        <stringValue>MICK</stringValue>
                    </attribute>
                    <attribute>
                        <attributeName>SiteID</attributeName>
                        <stringValue>278443</stringValue>
                    </attribute>
                    <attribute>
                        <attributeName>EndUserID</attributeName>
                        <stringValue>278443</stringValue>
                    </attribute>
                    <attribute>
                        <attributeName>SerialNumber</attributeName>
                        <stringValue>GSN157068801</stringValue>
                    </attribute>
                    <attribute>
                        <attributeName>OrderLineNo</attributeName>
                        <stringValue>1</stringValue>
                    </attribute>
                </lineItemAttributes>
            </entitlementLineItem>
        </searchEntitlementLineItemPropertiesResponse>
    </soapenv:Body>
</soapenv:Envelope>
'




;WITH XMLNAMESPACES(
     'http://schemas.xmlsoap.org/soap/envelope/'     as soapenv
)
SELECT
  
  t1.r.value('(./*:activationId/*:primaryKeys/*:activationId/text())[1]','varchar(100)') AS activationId
  ,t1.r.value('(./*:state/text())[1]',     'varchar(100)') AS [State]
  ,t1.r.value('(./*:entitlementId/*:primaryKeys/*:entitlementId/text())[1]',     'varchar(100)') AS entitlementId
  ,r.value('(./*:soldTo/*:primaryKeys/*:id/text())[1]','varchar(100)') AS soldTo
  ,r.value('(./*:soldToDisplayName/text())[1]','varchar(100)') AS soldToDisplayName
  ,r.value('(./*:entitlementState/text())[1]','varchar(100)') AS entitlementState
  ,r.value('(./*:allowPortalLogin/text())[1]','varchar(100)') AS allowPortalLogin
  ,r.value('(./*:product/*:primaryKeys/*:name/text())[1]','varchar(100)') AS ProductName
  ,r.value('(./*:product/*:primaryKeys/*:version/text())[1]','varchar(100)') AS ProductVersion
  ,r.value('(./*:productDescription/text())[1]','varchar(100)') AS productDescription
  ,r.value('(./*:partNumber/*:primaryKeys/*:partId/text())[1]','varchar(100)') AS partNumber
  ,r.value('(./*:partNumberDescription/text())[1]','varchar(100)') AS partNumberDescription
  ,r.value('(./*:licenseTechnology/*:primaryKeys/*:name/text())[1]','varchar(100)') AS LicenseTechnology
  ,r.value('(./*:licenseModel/*:primaryKeys/*:name/text())[1]','varchar(100)') AS licenseModel
  ,r.value('(./*:startDate/text())[1]','DateTime') AS startDate
  ,r.value('(./*:startDateOption/text())[1]','varchar(100)') AS startDateOption
  ,r.value('(./*:isPermanent/text())[1]','varchar(100)') AS isPermanent
  ,r.value('(./*:numberOfCopies/text())[1]','varchar(100)') AS numberOfCopies
  ,r.value('(./*:fulfilledAmount/text())[1]','varchar(100)') AS fulfilledAmount
  ,r.value('(./*:numberOfRemainingCopies/text())[1]','varchar(100)') AS numberOfRemainingCopies
  ,r.value('(./*:isTrusted/text())[1]','varchar(100)') AS isTrusted
  ,channelPartners.value('(./*:channelPartner/*:accountUnit/*:primaryKeys/*:id/text())[1]','varchar(100)') AS channelPartners
  ,channelPartners.value('(./*:channelPartner/*:contact/*:primaryKeys/*:id/text())[1]','varchar(100)') AS channelContact
  ,channelPartners.value('(./*:channelPartners/*:channelPartner/*:currentOwner/text())[1]','varchar(100)') AS currentOwner
  ,r.value('(./*:createdOnDateTime/text())[1]','DateTime') AS createdOnDateTime
  ,r.value('(./*:lastModifiedDateTime/text())[1]','DateTime') AS lastModifiedDateTime
  ,r.value('(./*:createdBy/text())[1]','varchar(100)') AS createdBy
  ,r.value('(./*:lastModifiedBy/text())[1]','varchar(100)') AS lastModifiedBy
  ,lineItemAttributes.value('(./*:attribute/attributeName/text())[1]','varchar(100)') AS attributeName
  ,lineItemAttributes.value('(./*:attribute/stringValue/text())[1]','varchar(100)') AS attributeValue
FROM @xmldata.nodes('/*:Envelope/*:Body/*:searchEntitlementLineItemPropertiesResponse/*:entitlementLineItem') AS t1(r)
    CROSS APPLY r.nodes('*:channelPartners')t2(channelPartners)    
    CROSS APPLY r.nodes('*:lineItemAttributes')t3(lineItemAttributes);

Please advise.

Thanks.

Regards,
Micheale

4

Hi,

Again. Just found the solution after spent hours:-

;WITH XMLNAMESPACES(
     'http://schemas.xmlsoap.org/soap/envelope/'     as soapenv
)
SELECT
  
  t1.r.value('(./*:activationId/*:primaryKeys/*:activationId/text())[1]','varchar(100)') AS activationId
  ,t1.r.value('(./*:state/text())[1]',     'varchar(100)') AS [State]
  ,t1.r.value('(./*:entitlementId/*:primaryKeys/*:entitlementId/text())[1]',     'varchar(100)') AS entitlementId
  ,r.value('(./*:soldTo/*:primaryKeys/*:id/text())[1]','varchar(100)') AS soldTo
  ,r.value('(./*:soldToDisplayName/text())[1]','varchar(100)') AS soldToDisplayName
  ,r.value('(./*:entitlementState/text())[1]','varchar(100)') AS entitlementState
  ,r.value('(./*:allowPortalLogin/text())[1]','varchar(100)') AS allowPortalLogin
  ,r.value('(./*:product/*:primaryKeys/*:name/text())[1]','varchar(100)') AS ProductName
  ,r.value('(./*:product/*:primaryKeys/*:version/text())[1]','varchar(100)') AS ProductVersion
  ,r.value('(./*:productDescription/text())[1]','varchar(100)') AS productDescription
  ,r.value('(./*:partNumber/*:primaryKeys/*:partId/text())[1]','varchar(100)') AS partNumber
  ,r.value('(./*:partNumberDescription/text())[1]','varchar(100)') AS partNumberDescription
  ,r.value('(./*:licenseTechnology/*:primaryKeys/*:name/text())[1]','varchar(100)') AS LicenseTechnology
  ,r.value('(./*:licenseModel/*:primaryKeys/*:name/text())[1]','varchar(100)') AS licenseModel
  ,r.value('(./*:startDate/text())[1]','DateTime') AS startDate
  ,r.value('(./*:startDateOption/text())[1]','varchar(100)') AS startDateOption
  ,r.value('(./*:isPermanent/text())[1]','varchar(100)') AS isPermanent
  ,r.value('(./*:numberOfCopies/text())[1]','varchar(100)') AS numberOfCopies
  ,r.value('(./*:fulfilledAmount/text())[1]','varchar(100)') AS fulfilledAmount
  ,r.value('(./*:numberOfRemainingCopies/text())[1]','varchar(100)') AS numberOfRemainingCopies
  ,r.value('(./*:isTrusted/text())[1]','varchar(100)') AS isTrusted
  ,r.value('(./*:createdOnDateTime/text())[1]','DateTime') AS createdOnDateTime
  ,r.value('(./*:lastModifiedDateTime/text())[1]','DateTime') AS lastModifiedDateTime
  ,r.value('(./*:createdBy/text())[1]','varchar(100)') AS createdBy
  ,r.value('(./*:lastModifiedBy/text())[1]','varchar(100)') AS lastModifiedBy
  ,t2.channelPartners.value('(./*:accountUnit/*:primaryKeys/*:id/text())[1]','varchar(100)') AS channelPartners
  ,t2.channelPartners.value('(./*:contact/*:primaryKeys/*:id/text())[1]','varchar(100)') AS channelContact
  ,t2.channelPartners.value('(./*:currentOwner/text())[1]','varchar(100)') AS currentOwner  
  ,t3.lineItemAttributes.value('(./*:attributeName/text())[1]','varchar(100)') AS attributeName
  ,t3.lineItemAttributes.value('(./*:stringValue/text())[1]','varchar(100)') AS attributeValue
FROM @xmldata.nodes('/*:Envelope/*:Body/*:searchEntitlementLineItemPropertiesResponse/*:entitlementLineItem') AS t1(r)
    CROSS APPLY t1.r.nodes('*:channelPartners/*:channelPartner')t2(channelPartners)    
    CROSS APPLY t1.r.nodes('*:lineItemAttributes/*:attribute')t3(lineItemAttributes);

Thanks.

Regards,
Micheale

1 Like