CODEWITHSUNDEEP
sqlsql-servert-sqlsubstring
Barış ERDOĞAN
Barış ERDOĞAN

Reputation: 109

SQL : How can I extract substring what I want from a long xml string?

I have a column which includes an XML value in my SQL table. I have to read the values in these three elements:

<POSTN_ID>0000-0000H1-POS</POSTN_ID>
<ROLE_ID>0000-00002B-ROL</ROLE_ID>
<STATUS>1</STATUS>

Which SQL methods can help me in this situation?

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

Upvotes: 0

Views: 1062

Answers (2)

AdamL
AdamL

Reputation: 13181

Use xPath/xQuery:

select cast('<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>' as xml) as col
into #tab
go

select
  col,
  nod,
  nod.query('./POSTN_ID/text()'),
  nod.value('./ROLE_ID[1]','varchar(255)')
from (
  select
    *, col.query('//PositionRoles/node()') as nod
  from #tab
) x

Upvotes: 1

arce.est
arce.est

Reputation: 379

The following syntax can help you, capture the objects you define and take them to a table model.

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)

Upvotes: 1

Related Questions