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

Yitzhak Khabinsky · Accepted Answer · 2020-03-17T16:13:51.650

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

Thanks for posting this code Yitzhak, this worked a treat for me. — Sarath, Mar 17 '20 at 05:35
Good to hear that the proposed solution is working for you. Please mark it as answered. You simply need to mark an answer as correct (the green check image). Click the green outlined checkmark to the left of the answer that solved your problem. This marks the answer as "accepted" — Yitzhak Khabinsky, Mar 17 '20 at 10:53

Shredding XML in using OpenXML

1 Answers1