Shredding XML in using OpenXML

Question

I am trying to shred the following XML, but I am unable to get any results using the OPENXML construct but my output does not look correct. Any suggestions on how I can re-write this?

<?xml version="1.0" encoding="UTF-8"?> <results
xmlns="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns"
xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
xs:schemaLocation="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns
https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/xsd">   
<result>
      <a>a1</a>
      <b>2</b>
      <c>a1332</c>
      <d>text.</d>
      <e>Risk 2</e>
      <f> </f>
      <g>a123</g>
      <h>1223324aaa</h>
      <i>l1245</i>
      <j>Complete</j>
      <k>Not yet reported</k>    </result>

Please note the following is the a snippet of the code I am using

  DECLARE @xml XML;
DECLARE @idoc INT;
SELECT @xml  = CONVERT(XML, cast(results AS VARCHAR(MAX)), 2) FROM stg.requirements;

EXEC sys.sp_xml_preparedocument @idoc OUTPUT
                               ,@xml
                               ,'<results xmlns="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns" xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xs:schemaLocation="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/xsd"/>';

SELECT *
FROM
    OPENXML(@idoc, '/*', 1)
    WITH ()

EXEC sys.sp_xml_removedocument @idoc;    

--SELECT * FROM #temp

DROP TABLE IF EXISTS #temp

Answer 1

Few things to point out.

(1) Your XML was not well-formed, so I had to fix it.

(2) Starting from SQL Server 2005 onwards, it is using XQuery language, based on the w3c standards, to deal with the XML data type. Microsoft's proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with obsolete SQL Server 2000. That's why use of the .nodes()

(3) Namespaces always shall be taken into account.

(4) Proper SQL Server data types in the .value() method.

SQL

DECLARE @xml XML = '<?xml version="1.0" encoding="UTF-8"?>
<results xmlns="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns"
         xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
         xs:schemaLocation="https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/xsd">
    <result>
        <a>a1</a>
        <b>2</b>
        <c>2020-02-15</c>
        <d>text.</d>
    </result>
        <result>
        <a>a7</a>
        <b>25</b>
        <c>2020-01-25</c>
        <d>Another text</d>
    </result>
</results>';

;WITH xmlnamespaces (DEFAULT 'https://crr.clm.ibmcloud.com/rs/query/1111/dataservice/ns')
SELECT c.value('(a/text())[1]', 'VARCHAR(10)') AS a
    , c.value('(b/text())[1]', 'INT') AS b
    , c.value('(c/text())[1]', 'DATE') AS c
    , c.value('(d/text())[1]', 'VARCHAR(30)') AS d
FROM @xml.nodes('/results/result') AS t(c);

Output

+----+----+------------+--------------+
| a  | b  |     c      |      d       |
+----+----+------------+--------------+
| a1 |  2 | 2020-02-15 | text.        |
| a7 | 25 | 2020-01-25 | Another text |
+----+----+------------+--------------+