‫دریافت مناسبت‌های سال 1388 از یک فایل XML

<?xml version="1.0" encoding="UTF-8"?>
<cal1388> 
        <day>
  <num>1/1</num>
  <desc>عید نوروز، لحظه تحویل سال:ساعت 15 و 13 دقیقه و 39 ثانیه </desc>
        </day>
</cal1388>

SELECT '1388' saal,
      X.day.query('num').value('.', 'nVARCHAR(50)') rooz_maah,
      X.day.query('desc').value('.', 'nVARCHAR(max)') tozih
FROM   (
          SELECT CAST(x AS XML)
          FROM   OPENROWSET(
                     BULK
                     'c:\1388.xml',
                     SINGLE_BLOB
                 ) AS T(x)
      ) AS T(x)
      CROSS APPLY x.nodes('cal1388/day') AS X(day);

DECLARE @MyXML XML
 
SELECT @MyXML = CAST(x AS XML)
FROM   OPENROWSET(
          BULK
          'c:\1388.xml',
          SINGLE_BLOB
      ) AS T(x)

SELECT 1388 saal,
      nref.value('num[1]', 'nvarchar(50)') rooz_maah,
      nref.value('desc[1]', 'nvarchar(max)') tozih

FROM   @MyXML.nodes('//day') AS R(nref)

DECLARE @MyXML XML
DECLARE @handle INT
 
SELECT @MyXML = CAST(x AS XML)
FROM   OPENROWSET(
          BULK
          'c:\1388.xml',
          SINGLE_BLOB
      ) AS T(x)

EXEC sp_xml_preparedocument @handle OUTPUT,
    @MyXML

SELECT 1388 saal,
      *
FROM   OPENXML(@handle, '/cal1388/day', 2) WITH
      (num VARCHAR(20), [desc] NVARCHAR(MAX))

EXEC sp_xml_removedocument @handle

INSERT INTO tblMonasebat
 (
   saal,
   rooz_mah,
   tozih
 )
SELECT '1388' saal,
      nref.value('num[1]', 'nvarchar(50)') rooz_maah,
      nref.value('desc[1]', 'nvarchar(max)') tozih
FROM   @MyXML.nodes('//day') AS R(nref)