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

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

can i doit with SQL Server OPENXML?

CodePudding user response：

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