XML Cross Apply Multiple Rows-CodePudding

Trying to get the following information using XML but struggling - could anyone assist? I need to get the repeating workparty info

i.e.

<pyWorkParty REPEATINGTYPE="PageGroup">
<rowdata REPEATINGINDEX="Underwriter">

I need this as one row with the next column being the name:

<pyFirstName>RANDOM</pyFirstName>

followed by the second repeating type being OrigUW and then third which is DATHandler

could anyone assist. ive had a go see below but wont work (i'm not really sure what Im doing as dont work with XMl data often)

SELECT
 t.TransID,
 --pyFullName.value('.','varchar(MAX)') as [Value],
 --pxPartyRole.value('.','varchar(MAX)') as [Value],
 --pyFullName.value('.','varchar(MAX)') as [Value],
 --t.TransXml,
 w.x.value('(pyWorkParty/RowData[@REPEATINGINDEX = "Underwriter"]/pyFullName/text())[1]', 'nvarchar(max)') as UndewrwriterName

FROM  #T t
CROSS APPLY TransXML.nodes('pagedata') as w(x)

<pagedata>
  <pyOrigUserWorkgroup>InternationalCasualty</pyOrigUserWorkgroup>
  <pxUpdateSystemID>pega</pxUpdateSystemID>
  <pzIndexCount>20</pzIndexCount>
  <pyResolvedOrgUnit>DelegatedAuthorities</pyResolvedOrgUnit>
  <pxUpdateDateTime>20220224T131425.174 GMT</pxUpdateDateTime>
  <pyResolvedTimestamp>20220224T131424.953 GMT</pyResolvedTimestamp>
  <pxApplication>DA</pxApplication>
  <pxCreateDateTime>20220208T160522.710 GMT</pxCreateDateTime>
  <pxUrgencyWork>0</pxUrgencyWork>
  <pyOrigOrgUnit>Casualty</pyOrigOrgUnit>
  <pxInsName>B-2511</pxInsName>
  <pyResolvedOrg>RANDONLondon</pyResolvedOrg>
  <pxSaveDateTime>20220224T131425.175 GMT</pxSaveDateTime>
  <pyResolvedUserWorkgroup>DelegatedAuthorities_DA</pyResolvedUserWorkgroup>
  <pxApplicationVersion>01.01.01</pxApplicationVersion>
  <pyHasAttachments>true</pyHasAttachments>
  <pzInsKey>RANDON-DA-WORK B-2511</pzInsKey>
  <pyOwnerOrgUnit>Casualty</pyOwnerOrgUnit>
  <pyOwnerOrg>RANDONLondon</pyOwnerOrg>
  <pyOrigUserID>OJakob</pyOrigUserID>
  <pyResolvedUserID>thill</pyResolvedUserID>
  <pxUpdateOpName>RANDOM(2) Hill</pxUpdateOpName>
  <pxUpdateCounter>38</pxUpdateCounter>
  <pxUpdateOperator>thill</pxUpdateOperator>
  <pxUpdateOrgUnit>DelegatedAuthorities</pxUpdateOrgUnit>
  <pyID>B-2511</pyID>
  <pyOwnerDivision>Underwriting</pyOwnerDivision>
  <pyStyle>pdf</pyStyle>
  <pyIsDataPageRefreshed>true</pyIsDataPageRefreshed>
  <pyWorkIDPrefix>B-</pyWorkIDPrefix>
  <pyOrigUserDivision>Underwriting</pyOrigUserDivision>
  <pyOrigDivision>Underwriting</pyOrigDivision>
  <pyResolvedTime>1372141.0</pyResolvedTime>
  <pyResolvedDivision>RiskAndCompliance</pyResolvedDivision>
  <pxObjClass>RANDON-DA-Work-Binder</pxObjClass>
  <pxCreateOperator>OJakob</pxCreateOperator>
  <pyOrigOrg>RANDONLondon</pyOrigOrg>
  <pyResolvedUserDivision>RiskAndCompliance</pyResolvedUserDivision>
  <pzIndexOwnerKey>RANDON-DA-WORK B-2511</pzIndexOwnerKey>
  <BinderSectionOBRef>22N44389AANR</BinderSectionOBRef>
  <pxCreateSystemID>pega</pxCreateSystemID>
  <pyLabel>INSURANCE COBROKERS INC</pyLabel>
  <pxCreateOpName>RANDOM Jakob</pxCreateOpName>
  <pzStatus>valid</pzStatus>
  <Queries REPEATINGTYPE="PageList" />
  <Compliance>
    <pxObjClass>RANDON-DA-Data-Compliance</pxObjClass>
  </Compliance>
  <CaseDetails>
    <ExpiryDate>20230228T000000.000 GMT</ExpiryDate>
    <BusinessType>New</BusinessType>
    <CreateDate>20220208</CreateDate>
    <Name>INSURANCE BROKERSBROKERS INC</Name>
    <pxObjClass>RANDON-DA-Data-CaseDetails</pxObjClass>
    <UniqRef>11511</UniqRef>
    <InceptionDate>20220301T000000.000 GMT</InceptionDate>
    <CaseStatus>Approved</CaseStatus>
    <TaskType>NB</TaskType>
    <TaskTypeLabel>New Binder</TaskTypeLabel>
    <AGReferenceID>2202AG11511BI</AGReferenceID>
    <AssignDate REPEATINGTYPE="PropertyGroup">
      <rowdata REPEATINGINDEX="UW">20220217</rowdata>
      <rowdata REPEATINGINDEX="DA">20220224</rowdata>
    </AssignDate>
    <AssignmentStatus REPEATINGTYPE="PropertyGroup">
      <rowdata REPEATINGINDEX="UW">New</rowdata>
      <rowdata REPEATINGINDEX="DA">Resolved</rowdata>
    </AssignmentStatus>
  </CaseDetails>
  <pyWorkParty REPEATINGTYPE="PageGroup">
    <rowdata REPEATINGINDEX="Underwriter">
      <pyFullName>RANDOM Jakob</pyFullName>
      <pyWorkPartyUri>OJakob</pyWorkPartyUri>
      <pxPartyRole>Underwriter</pxPartyRole>
      <pyUserName>RANDOM Jakob</pyUserName>
      <pxObjClass>Data-Party-Operator</pxObjClass>
      <pyFirstName>RANDOM</pyFirstName>
      <pyUserIdentifier>OJakob</pyUserIdentifier>
      <pyPartyLabel>Underwriter Handler</pyPartyLabel>
      <pzIndexOwnerKey>RANDON-DA-WORK B-2511</pzIndexOwnerKey>
      <pyLabel>RANDOM Jakob</pyLabel>
      <pxSubscript>Underwriter</pxSubscript>
      <pyLastName>Jakob</pyLastName>
      <pyWorkBasket>UWCasualty</pyWorkBasket>
      <pyEmail1>[email protected]</pyEmail1>
      <pzIndexes REPEATINGTYPE="PropertyGroup">
        <rowdata REPEATINGINDEX="PartyURI">1</rowdata>
      </pzIndexes>
    </rowdata>
    <rowdata REPEATINGINDEX="OrigUW">
      <pyFullName>RANDOM Jakob</pyFullName>
      <pyWorkPartyUri>OJakob</pyWorkPartyUri>
      <pxPartyRole>OrigUW</pxPartyRole>
      <pyUserName>RANDOM Jakob</pyUserName>
      <pxObjClass>Data-Party-Operator</pxObjClass>
      <pyFirstName>RANDOM</pyFirstName>
      <pyUserIdentifier>OJakob</pyUserIdentifier>
      <pyPartyLabel>Underwriter</pyPartyLabel>
      <pzIndexOwnerKey>RANDON-DA-WORK B-2511</pzIndexOwnerKey>
      <pyLabel>RANDOM Jakob</pyLabel>
      <pxSubscript>OrigUW</pxSubscript>
      <pyLastName>Jakob</pyLastName>
      <pyWorkBasket>UWCasualty</pyWorkBasket>
      <pyEmail1>[email protected]</pyEmail1>
      <pzIndexes REPEATINGTYPE="PropertyGroup">
        <rowdata REPEATINGINDEX="PartyURI">2</rowdata>
      </pzIndexes>
    </rowdata>
    <rowdata REPEATINGINDEX="DATHandler">
      <pyFullName>RANDOM(2) Hill</pyFullName>
      <pyWorkPartyUri>thill</pyWorkPartyUri>
      <pxPartyRole>DATHandler</pxPartyRole>
      <pyUserName>RANDOM(2) Hill</pyUserName>
      <pxObjClass>Data-Party-Operator</pxObjClass>
      <pyFirstName>RANDOM(2)</pyFirstName>
      <pyUserIdentifier>thill</pyUserIdentifier>
      <pyPartyLabel>DAT Handler</pyPartyLabel>
      <pzIndexOwnerKey>RANDON-DA-WORK B-2511</pzIndexOwnerKey>
      <pyLabel>RANDOM(2) Hill</pyLabel>
      <pxSubscript>DATHandler</pxSubscript>
      <pyLastName>Hill</pyLastName>
      <pyWorkBasket>DelegatedAuthorities_DA</pyWorkBasket>
      <pyEmail1>[email protected]</pyEmail1>
      <pzIndexes REPEATINGTYPE="PropertyGroup">
        <rowdata REPEATINGINDEX="PartyURI">19</rowdata>
      </pzIndexes>
    </rowdata>
  </pyWorkParty>
</pagedata>

CodePudding user response：

It's not altogether clear from your question what your expected results are.

From the example result it seems that you're wanting the XML of the required elements, such as via:

SELECT
  t.TransID,
  pyWorkParty.query(N'(rowdata[@REPEATINGINDEX="Underwriter"]/pyFullName)[1]') as UndewrwriterName,
  pyWorkParty.query(N'(rowdata[@REPEATINGINDEX="OrigUW"]/pyFullName)[1]') as OriginalUnderwriter,
  pyWorkParty.query(N'(rowdata[@REPEATINGINDEX="DATHandler"]/pyFullName)[1]') as DelegatedAuthorityTeam
FROM  #T t
CROSS APPLY TransXML.nodes('pagedata/pyWorkParty[@REPEATINGTYPE="PageGroup"]') as pagedata(pyWorkParty);

But from the attempted SQL query it seems like you're wanting the text contents of those elements, such as via:

SELECT
  t.TransID,
  pyWorkParty.value(N'(rowdata[@REPEATINGINDEX="Underwriter"]/pyFullName/text())[1]', N'nvarchar(max)') as UndewrwriterName,
  pyWorkParty.value(N'(rowdata[@REPEATINGINDEX="OrigUW"]/pyFullName/text())[1]', N'nvarchar(max)') as OriginalUnderwriter,
  pyWorkParty.value(N'(rowdata[@REPEATINGINDEX="DATHandler"]/pyFullName/text())[1]', N'nvarchar(max)') as DelegatedAuthorityTeam
FROM  #T t
CROSS APPLY TransXML.nodes('pagedata/pyWorkParty[@REPEATINGTYPE="PageGroup"]') as pagedata(pyWorkParty);

CodePudding user response：

Try this code:

select  G.n.value('./@REPEATINGTYPE', 'nvarchar(50)') as RType
,       A.n.value('./@REPEATINGINDEX', 'nvarchar(50)') as RIndex
,       A.n.value('pyUserName[1]', 'nvarchar(50)') as UserName
from @xmltbl t
cross apply [data].nodes('pagedata/pyWorkParty') G(n)
cross apply g.n.nodes('rowdata') A(n)

If it doesn't bring you what you want, please, provide some sample table with desired result, because from your question it isn't very clear.