SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

declare @xml xml ='<PatentInformationResponse xmlns="http://www.iponz.govt.nz/XMLSchema/patents/information" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <TransactionDateTime>2022-12-04T01:01:03.4406179+13:00</TransactionDateTime>
  <PatentInformation>
    <Patent xmlns="http://www.iponz.govt.nz/XMLSchema/patents">
      <PatentNumber>306435</PatentNumber>
      <InternationalApplicationNumber>PCT/EP1237/0075454</InternationalApplicationNumber>
      <WIPOPublicationNumber>WO/1990/0294511</WIPOPublicationNumber>
      <PatentCurrentStatusCode>Lapsed</PatentCurrentStatusCode>
      <PatentCurrentStatusDate>2008-06-16</PatentCurrentStatusDate>
      <CompleteFiledDate>1997-12-15</CompleteFiledDate>
      <PriorityDetails>
        <Priority>
          <PriorityCountryCode>GB</PriorityCountryCode>
          <PriorityNumber>96 9627006</PriorityNumber>
          <PriorityDate>1996-12-27</PriorityDate>
        </Priority>
      </PriorityDetails>
      <NationalPhaseEntryDate>1999-06-17</NationalPhaseEntryDate>
      <PublishedDate>2000-09-29</PublishedDate>
      <ProvisionalDate>1997-12-15</ProvisionalDate>
      <GrantDate>2001-01-11</GrantDate>
      <RenewalDueDate>2007-12-15</RenewalDueDate>
      <ExpiryDate>2017-12-15</ExpiryDate>
      <PatentType>National Phase Entry PCT</PatentType>
      <PatentTitle>Sulfonamide compounds having 5-HT receptor activity</PatentTitle>
      <PatentAbstract>
Patent 336328

Compounds of formula (I) and pharmaceutically acceptable salts thereof in which

A and B are -O-;

g is 0, 1, 2, 3 or 4;

R1 represents an alkyl group containing 1 to 3 carbon atoms optionally substituted by one or more halo; an alkoxy group containing 1 to 3 carbon atoms optionally substituted by one or more halo; halo; 6,7-methylenedioxy optionally C-substituted by one or two alkyl groups containing 1 to 3 carbon atoms; or an alkylthio group containing 1 to 3 carbon atoms optionally substituted by one or more halo; the substituents represented by R1 being the same or different when g is 2, 3 or 4;
R2 is H, an alkyl group containing 1 to 3 carbon atoms, or an alkoxy group containing 1 to 3 carbon atoms;
R3 and R4, which are the same or different, are H, or an alkyl group containing 1 to 3 carbon atoms; 
U is an alkylene chain optionally substituted by one or more alkyl;

Q is a group of formula (IIc), in which V is methylene, E and E'' are ethylene and R6 is H;

T represents phenyl, 1- or 2-naphthyl, 5-naphth[2,1-o][1 ,2,3]oxadiazolyl, 2-, 3- or 4-pyridyl, 2-, 4- or 5-pyrimidinyl, 2- or 3-thienyl, 2- or 3-furyl, 2-, 3- or 7-benzo[b]furanyl, 2,3-dihydro-7-benzo[b]furanyl, 2-, 3- or 7-benzo[b]thiophenyl, 3-, 4- or 5-pyrazolyl, 1,2,3triazol-4-yl, 1,2,3-triazol-5-yl, 1,2,4-triazol-2-yl, 5-tetrazolyl, 2-, 3- or 4-quinolinyl, 2- or 4quinazolinyl, 3-, 4- or 5-isoxazolyl, 2-, 4- or 5-oxazolyl, 3-, 4- or 5-isothiazolyl or 2-, 4- or 5-thiazolyl each of which may be optionally substituted by one or more substituents selected from a) halo, b) an alkyl group containing 1 to 4 carbon atoms optionally substituted by one or more halo, c) an alkoxy group containing 1 to 3 carbon atoms optionally substituted by one or more halo, d) an alkylthio group containing 1 to 3 carbon atoms optionally substituted by one or more halo, e) hydroxy, f) an acyloxy group containing 1 to 3 carbon atoms, g) hydroxymethyl, h) cyano, i) an alkanoyl group containing 1 to 6 carbon atoms, j) an alkoxycarbonyl group containing 2 to 6 carbon atoms, k) a carbamoyl group or carbamoylmethyl group each optionally N-substituted by one or two alkyl groups each containing 1 to 3 carbon atoms, I)a sulphamoyl or sulphamoylmethyl group each optionally N-substituted by one or two alkyl groups each containing 1 to 3 carbon atoms, m) an amino group optionally substituted by one or two alkyl groups each containing 1 to 5 carbon atoms, n) 1 -pyrrolidinyl or 1 -piperidinyl, o) nitro or p) acetamido.
</PatentAbstract>
      <ClassificationDetails>
        <Classification>
          <ClassificationKindCode>IPC</ClassificationKindCode>
          <ClassificationVersion>2</ClassificationVersion>
          <ClassDescriptionDetails>
            <ClassDescription>C07D 413/14</ClassDescription>
          </ClassDescriptionDetails>
        </Classification>
        <Classification>
          <ClassificationKindCode>IPC</ClassificationKindCode>
          <ClassificationVersion>2</ClassificationVersion>
          <ClassDescriptionDetails>
            <ClassDescription>C07D 493/04</ClassDescription>
          </ClassDescriptionDetails>
        </Classification>
        <Classification>
          <ClassificationKindCode>IPC</ClassificationKindCode>
          <ClassificationVersion>2</ClassificationVersion>
          <ClassDescriptionDetails>
            <ClassDescription>C07D 405/12</ClassDescription>
          </ClassDescriptionDetails>
        </Classification>
        <Classification>
          <ClassificationKindCode>IPC</ClassificationKindCode>
          <ClassificationVersion>2</ClassificationVersion>
          <ClassDescriptionDetails>
            <ClassDescription>C07D 405/06</ClassDescription>
          </ClassDescriptionDetails>
        </Classification>
        <Classification>
          <ClassificationKindCode>IPC</ClassificationKindCode>
          <ClassificationVersion>2</ClassificationVersion>
          <ClassDescriptionDetails>
            <ClassDescription>A61K 31/445</ClassDescription>
          </ClassDescriptionDetails>
        </Classification>
        <Classification>
          <ClassificationKindCode>IPC</ClassificationKindCode>
          <ClassificationVersion>2</ClassificationVersion>
          <ClassDescriptionDetails>
            <ClassDescription>C07D 405/14</ClassDescription>
          </ClassDescriptionDetails>
        </Classification>
      </ClassificationDetails>
      <PatentDocumentDetails>
        <PatentDocument>
          <DocumentType>1202</DocumentType>
          <DocumentFilename>Abstract</DocumentFilename>
          <DocumentFileFormat>PDF</DocumentFileFormat>
          <DocumentSizeInByte>6799</DocumentSizeInByte>
          <DocumentID>0900000180614a42</DocumentID>
        </PatentDocument>
        <PatentDocument>
          <DocumentType>1203</DocumentType>
          <DocumentFilename>Drawing</DocumentFilename>
          <DocumentFileFormat>PDF</DocumentFileFormat>
          <DocumentSizeInByte>6738</DocumentSizeInByte>
          <DocumentID>0900000180534c18</DocumentID>
        </PatentDocument>
        <PatentDocument>
          <DocumentType>1209</DocumentType>
          <DocumentFilename>Complete Specifications</DocumentFilename>
          <DocumentFileFormat>PDF</DocumentFileFormat>
          <DocumentSizeInByte>1887437</DocumentSizeInByte>
          <DocumentID>090000018052fa76</DocumentID>
        </PatentDocument>
      </PatentDocumentDetails>
      <ApplicantDetails>
        <Applicant>
          <ApplicantKey>
            <Identifier>111359</Identifier>
          </ApplicantKey>
          <ApplicantLegalEntity>Person</ApplicantLegalEntity>
          <ApplicantIncorporationCountryCode>DE</ApplicantIncorporationCountryCode>
          <ApplicantAddressBook>
            <FormattedNameAddress>
              <Name>
                <FreeFormatName>
                  <FreeFormatNameDetails>
                    <FreeFormatNameLine languageCode="en" sequenceNumber="1">KNOLL AKTIENGESELLSCHAFT</FreeFormatNameLine>
                  </FreeFormatNameDetails>
                </FreeFormatName>
              </Name>
              <Address>
                <AddressCountryCode>DE</AddressCountryCode>
                <FormattedAddress>
                  <AddressLine languageCode="en" sequenceNumber="1">Knollstrasse</AddressLine>
                  <AddressCity>D-67061 Ludwigshafen</AddressCity>
                </FormattedAddress>
              </Address>
            </FormattedNameAddress>
            <ContactInformationDetails>
              <Phone>
                <CountryCode />
                <AreaCode />
                <Number />
              </Phone>
            </ContactInformationDetails>
            <PostalAddress>
              <AddressCountryCode>DE</AddressCountryCode>
              <FormattedAddress>
                <AddressLine languageCode="en" sequenceNumber="1">Knollstrasse</AddressLine>
                <AddressCity>D-67061 Ludwigshafen</AddressCity>
              </FormattedAddress>
            </PostalAddress>
          </ApplicantAddressBook>
        </Applicant>
      </ApplicantDetails>
      <RepresentativeDetails>
        <Representative>
          <RepresentativeKey>
            <Identifier>600022</Identifier>
          </RepresentativeKey>
          <RepresentativeLegalEntity>Person</RepresentativeLegalEntity>
          <RepresentativeIncorporationCountryCode>NZ</RepresentativeIncorporationCountryCode>
          <RepresentativeAddressBook>
            <FormattedNameAddress>
              <Name>
                <FreeFormatName>
                  <FreeFormatNameDetails>
                    <FreeFormatNameLine languageCode="en" sequenceNumber="1">AJ PARK</FreeFormatNameLine>
                  </FreeFormatNameDetails>
                </FreeFormatName>
              </Name>
              <Address>
                <AddressCountryCode>NZ</AddressCountryCode>
                <FormattedAddress>
                  <AddressLine languageCode="en" sequenceNumber="1">Aon Centre</AddressLine>
                  <AddressSuburb>Level 22</AddressSuburb>
                  <AddressCity>Wellington 6011</AddressCity>
                  <AddressState>1 Willis Street</AddressState>
                </FormattedAddress>
              </Address>
            </FormattedNameAddress>
            <ContactInformationDetails>
              <Phone>
                <CountryCode />
                <AreaCode />
                <Number>64 4473 8278</Number>
              </Phone>
            </ContactInformationDetails>
            <PostalAddress>
              <AddressCountryCode>NZ</AddressCountryCode>
              <FormattedAddress>
                <AddressLine languageCode="en" sequenceNumber="1">PO Box 949</AddressLine>
                <AddressCity>Wellington</AddressCity>
                <AddressPostcode>6140</AddressPostcode>
              </FormattedAddress>
            </PostalAddress>
          </RepresentativeAddressBook>
        </Representative>
      </RepresentativeDetails>
      <InventorDetails>
        <Inventor>
          <Name>
            <FreeFormatName>
              <NameKind>Natural Person</NameKind>
              <FreeFormatNameDetails>
                <FreeFormatNameLine languageCode="en" sequenceNumber="1">Wishart, Neil</FreeFormatNameLine>
              </FreeFormatNameDetails>
            </FreeFormatName>
          </Name>
        </Inventor>
        <Inventor>
          <Name>
            <FreeFormatName>
              <NameKind>Natural Person</NameKind>
              <FreeFormatNameDetails>
                <FreeFormatNameLine languageCode="en" sequenceNumber="1">Birch, Alan Martin</FreeFormatNameLine>
              </FreeFormatNameDetails>
            </FreeFormatName>
          </Name>
        </Inventor>
      </InventorDetails>
      <LicenseeDetails />
      <LastRenewedBy />
      <AddressForService>
        <AddressCountryCode>NZ</AddressCountryCode>
        <FormattedAddress>
          <AddressLine languageCode="en" sequenceNumber="1">Aon Centre</AddressLine>
          <AddressSuburb>Level 22</AddressSuburb>
          <AddressCity>Wellington 6011</AddressCity>
          <AddressState>1 Willis Street</AddressState>
        </FormattedAddress>
      </AddressForService>
      <PatentEventDetails>
        <PatentEvent>
          <PatentEventCode>PAT_PCTAPP</PatentEventCode>
          <PatentEventCompletedDate>1997-12-05</PatentEventCompletedDate>
          <PatentEventDueDate>1997-12-05</PatentEventDueDate>
          <PatentEventJournalPublicationDate>1997-12-05</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IPONZ_EDIT</PatentEventCode>
          <PatentEventCompletedDate>2000-08-31</PatentEventCompletedDate>
          <PatentEventDueDate>2000-08-31</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2000-08-31</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IPONZ_EDIT</PatentEventCode>
          <PatentEventCompletedDate>2000-08-31</PatentEventCompletedDate>
          <PatentEventDueDate>2000-08-31</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2000-08-31</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IPONZ_EDIT</PatentEventCode>
          <PatentEventCompletedDate>2000-09-06</PatentEventCompletedDate>
          <PatentEventDueDate>2000-09-06</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2000-09-06</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>PT_ACCEPT</PatentEventCode>
          <PatentEventCompletedDate>2000-09-29</PatentEventCompletedDate>
          <PatentEventDueDate>2000-09-13</PatentEventDueDate>
          <PatentEventJournalIssue>1455</PatentEventJournalIssue>
          <PatentEventJournalPublicationDate>2000-09-29</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IPONZ_EDIT</PatentEventCode>
          <PatentEventCompletedDate>2000-09-18</PatentEventCompletedDate>
          <PatentEventDueDate>2000-09-18</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2000-09-18</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>PT_Granted</PatentEventCode>
          <PatentEventCompletedDate>2001-01-26</PatentEventCompletedDate>
          <PatentEventDueDate>2001-01-11</PatentEventDueDate>
          <PatentEventJournalIssue>1459</PatentEventJournalIssue>
          <PatentEventJournalPublicationDate>2001-01-26</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IPONZ_EDIT</PatentEventCode>
          <PatentEventCompletedDate>2001-01-17</PatentEventCompletedDate>
          <PatentEventDueDate>2001-01-17</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2001-01-17</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IP_RENEW</PatentEventCode>
          <PatentEventCompletedDate>2001-10-26</PatentEventCompletedDate>
          <PatentEventDueDate>2001-10-01</PatentEventDueDate>
          <PatentEventJournalIssue>1468</PatentEventJournalIssue>
          <PatentEventJournalPublicationDate>2001-10-26</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IP_RENEW</PatentEventCode>
          <PatentEventCompletedDate>2001-10-01</PatentEventCompletedDate>
          <PatentEventDueDate>2001-10-01</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2001-10-01</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IP_RENEW</PatentEventCode>
          <PatentEventCompletedDate>2004-10-29</PatentEventCompletedDate>
          <PatentEventDueDate>2004-09-20</PatentEventDueDate>
          <PatentEventJournalIssue>1504</PatentEventJournalIssue>
          <PatentEventJournalPublicationDate>2004-10-29</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>IP_RENEW</PatentEventCode>
          <PatentEventCompletedDate>2004-09-20</PatentEventCompletedDate>
          <PatentEventDueDate>2004-09-20</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2004-09-20</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>PT_LAPSED</PatentEventCode>
          <PatentEventCompletedDate>2008-06-16</PatentEventCompletedDate>
          <PatentEventDueDate>2008-06-16</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2008-06-16</PatentEventJournalPublicationDate>
        </PatentEvent>
        <PatentEvent>
          <PatentEventCode>CRI_AUTH</PatentEventCode>
          <PatentEventCompletedDate>2020-10-19</PatentEventCompletedDate>
          <PatentEventDueDate>2020-10-19</PatentEventDueDate>
          <PatentEventJournalPublicationDate>2020-10-20</PatentEventJournalPublicationDate>
        </PatentEvent>
      </PatentEventDetails>
    </Patent>
  </PatentInformation>
</PatentInformationResponse>';

;WITH XMLNAMESPACES ('http://www.iponz.govt.nz/XMLSchema/patents' as pinfor,
				'http://www.iponz.govt.nz/XMLSchema/patents/information' as pinfo)
--SELECT n.n.value('(./text())[1]','varchar(50)') as PatentNumber
--FROM @xml.nodes('pinfo:PatentInformationResponse/pinfo:PatentInformation/pinfor:Patent/pinfor:PatentNumber') as n(n);

--SELECT n.n.value('(./text())[1]','varchar(50)') as PatentNumber
--FROM @xml.nodes('*:PatentInformationResponse/*:PatentInformation/*:Patent/*:PatentNumber') as n(n);

SELECT n.n.value('(pinfor:PatentNumber/text())[1]','varchar(50)') as PatentNumber,
       n.n.value('(pinfor:InternationalApplicationNumber/text())[1]','varchar(50)') as InternationalApplicationNumber,
	   n.n.value('(pinfor:WIPOPublicationNumber/text())[1]','varchar(50)') as WIPOPublicationNumber,
	   n.n.value('(pinfor:PatentCurrentStatusCode/text())[1]','varchar(50)') as PatentCurrentStatusCode,
	   n.n.value('(pinfor:PatentCurrentStatusDate/text())[1]','date') as PatentCurrentStatusDate,
	   n.n.value('(pinfor:CompleteFiledDate/text())[1]','date') as CompleteFiledDate
FROM @xml.nodes('pinfo:PatentInformationResponse/pinfo:PatentInformation/pinfor:Patent') as n(n)

PatentNumber	InternationalApplicationNumber	WIPOPublicationNumber	PatentCurrentStatusCode	PatentCurrentStatusDate	CompleteFiledDate
306435	PCT/EP1237/0075454	WO/1990/0294511	Lapsed	2008-06-16	1997-12-15