SQL：如何从长xml字符串中提取想要的子字符串？

以下语法可以帮助您，捕获定义的对象并将其带到表模型中。

DECLARE @xml xml = '
<parameter name="OrgHierarchyDatasets_diffgram">
  <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
    <NewDataSet>
      <PositionRoles diffgr:id="PositionRoles1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
          <POSTN_ID>0000-0000H1-POS</POSTN_ID>
          <ROLE_ID>0000-00002B-ROL</ROLE_ID>
          <STATUS>1</STATUS>
      </PositionRoles>
    </NewDataSet>
  </diffgr:diffgram>
</parameter>
<parameter name="ExistNodeCheck" type="System.Boolean">True</parameter>'

SELECT  
       Tbl.Col.value('POSTN_ID[1]', 'nvarchar(50)') AS POSTN_ID,
       Tbl.Col.value('ROLE_ID[1]', 'nvarchar(50)') AS ROLE_ID,
       Tbl.Col.value('STATUS[1]', 'nvarchar(50)') AS STATUS
FROM   @xml.nodes('//PositionRoles') Tbl(Col)