Using OPENXML for extracting data

Question

i have xml text, and want extract data

DECLARE @d_txt  VARCHAR(max)
SET @d_txt = '
<DI_List>
  <SDI>
    <a>6559864</a>
    <DI>
      <Id>3036780478</Id>
      <mic>
        <date>2022-11-13</date>
        <kod>774673</kod>
      </mic>
      <mic>
        <date>2022-11-11</date>
        <kod>774673</kod>
      </mic>  
    </DI>
  </SDI>
</DI_List>'

DECLARE @d_xml  INT

exec sp_xml_preparedocument @d_xml output, @d_txt;

        SELECT *
        FROM OPENXML(@d_xml, '/DI_List/SDI', 0) WITH
        (
            Id  VARCHAR(30) 'DI/Id',
            nmic XML 'DI/mic'
        )

EXEC sys.sp_xml_removedocument @d_xml

i got

Id	nmic
3036780478	<mic><date>2022-11-13</date><kod>774673</kod></mic>

but i want nmic field contains both nodes, not first only

Id	nmic
3036780478	<mic><date>2022-11-13</date><kod>774673</kod></mic><mic><date>2022-11-11</date><kod>774673</kod></mic>

can i doit with SQL Server OPENXML?

Answer 1

As mentioned in the comments, use XQuery instead of OPENXML; OPENXML is a far older and "clunky" way of querying XML. Also, your XML is in the wrong datatype, there is an xml data type for XML.

If you switch to XQuery, and change the data type, this is much easier:

DECLARE @d_txt  xml --varchar is the wrong datatype
SET @d_txt = '
<DI_List>
  <SDI>
    <a>6559864</a>
    <DI>
      <Id>3036780478</Id>
      <mic>
        <date>2022-11-13</date>
        <kod>774673</kod>
      </mic>
      <mic>
        <date>2022-11-11</date>
        <kod>774673</kod>
      </mic>  
    </DI>
  </SDI>
</DI_List>';


SELECT SDI.DI.value('(Id/text())[1]', 'bigint') AS Id,
       SDI.DI.query('mic') AS nmic
FROM @d_txt.nodes('DI_List/SDI/DI')SDI(DI);

db<>fiddle