Convert XML-Blob to relational table

Question

I have a table in SQL Server 2017 including XML-blobs and I want to convert the data into relational tables. I'm trying to use OPENXML and the sp_xml_preparedocument procedure in SQL Server. The XML-blob is a little bit special (as usual) and I can't find the right way to write the code. See the example. Please, anyone, help me

DECLARE @XML AS XML, @hDoc AS INT

SET @XML = '<reports xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:d1p1="http://www.uc.se/schemas/ucOrderReply/" d1p1:lang="swe">
                                       <d1p1:report d1p1:id="6611231234" d1p1:name="Full name" d1p1:styp="F31" d1p1:index="0">
                                                  <d1p1:group d1p1:id="W080" d1p1:index="0" d1p1:key="" d1p1:name="ID-uppgifter, fysiker">
                                                              <d1p1:term d1p1:id="W08001">9761123768</d1p1:term>
                                                              <d1p1:term d1p1:id="W08002">6611231234</d1p1:term>
                                                              <d1p1:term d1p1:id="W08003">First name</d1p1:term>
                                                              <d1p1:term d1p1:id="W08004">Old road 174</d1p1:term>
                                                              <d1p1:term d1p1:id="W08005">17464</d1p1:term>
                                                              <d1p1:term d1p1:id="W08006">city</d1p1:term>
                                                  </d1p1:group>
                                       </d1p1:report>
                                     </reports>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<reports xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:d1p1="http://www.uc.se/schemas/ucOrderReply/" d1p1:lang="swe"/>'

SELECT id--, name, W08001, W08002, W08003, W08004, W08005, W08006            -- This is what I want
FROM OPENXML(@hDoc, 'd1p1:reports/d1p1:report/d1p1:group/d1p1:term') -- I'm not sure about this path
WITH
(
id [varchar](50) '@id'   -- This is not work
--, name
--, W08001
--, W08002
--, W08003
--, W08004
--, W08005
--, W08006
)

EXEC sp_xml_removedocument @hDoc
GO

https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/ — JGFMK, Dec 26 '19 at 11:54

score 2 · Accepted Answer · answered Dec 26 '19 at 13:10

2

Given your XML, you must respect the XML namespaces defined in the XML document! Also, I'd strongly recommend using the built-in XQuery function and avoid the old OPENXML stuff.

So try this:

;WITH XMLNAMESPACES('http://www.uc.se/schemas/ucOrderReply/' AS ns)
SELECT
    Id = xc.value('@ns:id', 'bigint'),
    Name = xc.value('@ns:name', 'varchar(25)'),
    NodeValue = xc2.value('(.)', 'varchar(50)')
FROM
    @XML.nodes('/reports/ns:report') AS XT(XC)
CROSS APPLY
    xc.nodes('ns:group/ns:term') AS XT2(XC2)

This should return something like this:

answered Dec 26 '19 at 13:10

marc_s

732,580
175
1,330
1,459

Good answer, +1 from my side! – Yitzhak Khabinsky Dec 26 '19 at 16:42
1

Thank you very much. It works find. I didn't know about XQuery. I'm so happy! – Torbjörn Dec 26 '19 at 20:20

score 0 · Answer 2 · answered Feb 21 '20 at 17:30

I know there is an answer already but for the sole purpose of helping others this is what I'm using right now. I created a table-valued function as follows (see below). The good thing is that it doesn't matter which XML you pass to the function it will convert it to a table that you can query, insert in another table, etc. It has become very useful for me.

Note: Some xml has a few special characters at the top element with namespaces, etc. Some of them need to be clean a little bit before use the function but that happend to me in less than 1% of the cases I used it.

Usage: Select * from [Utility].FlattenXml

CREATE FUNCTION [Utility].[FlattenXml](@xmlDoc XML)  
RETURNS TABLE 
AS RETURN 
WITH CTE AS ( 

    SELECT 
            1 AS lvl,  
            x.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
            CAST(NULL AS NVARCHAR(MAX)) AS ParentName, 
            CAST(1 AS INT) AS ParentPosition, 
            CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
            x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,  
            x.value('local-name(.)','NVARCHAR(MAX)')  
            + N'[' 
            + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)  
            + N']' AS XPath,  
            ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position, 
            x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,  
            x.value('text()[1]','NVARCHAR(MAX)') AS Value,  
            x.query('.') AS this,         
            x.query('*') AS t,  
            CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,  
            CAST(1 AS INT) AS ID  
    FROM @xmlDoc.nodes('/*') a(x)  
    UNION ALL 
    SELECT 
            p.lvl + 1 AS lvl,  
            c.value('local-name(.)','NVARCHAR(MAX)') AS Name,  
            CAST(p.Name AS NVARCHAR(MAX)) AS ParentName, 
            CAST(p.Position AS INT) AS ParentPosition, 
            CAST(N'Element' AS NVARCHAR(20)) AS NodeType,  
            CAST(p.FullPath + N'/' + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS FullPath,  
            CAST(p.XPath + N'/'+ c.value('local-name(.)','NVARCHAR(MAX)')+ N'['+ CAST(ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)') 
            ORDER BY (SELECT 1)) AS NVARCHAR)+ N']' AS NVARCHAR(MAX)) AS XPath,  
            ROW_NUMBER() OVER(PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
            ORDER BY (SELECT 1)) AS Position, 
            CAST( SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1) + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)) AS Tree,  
            CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, c.query('.') AS this,  
            c.query('*') AS t,  
            CAST(p.Sort + CAST( (lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)) AS VARBINARY(MAX) ) AS Sort,  
            CAST((lvl + 1) * 1024 + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT)

    FROM CTE p  
    CROSS APPLY p.t.nodes('*') b(c)), cte2 AS (  
                                                SELECT 
                                                lvl AS Depth,  
                                                Name AS NodeName,  
                                                ParentName, 
                                                ParentPosition, 
                                                NodeType,  
                                                FullPath,  
                                                XPath,  
                                                Position, 
                                                Tree AS TreeView,  
                                                Value,  
                                                this AS XMLData,  
                                                Sort, 
                                                ID
                                                FROM cte  
    UNION ALL 
    SELECT 
            p.lvl,  
            x.value('local-name(.)','NVARCHAR(MAX)'),  
            p.Name, 
            p.Position, 
            CAST(N'Attribute' AS NVARCHAR(20)),  
            p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
            p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
            1, 
            SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)  
            + N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),  
            x.value('.','NVARCHAR(MAX)'),  
            NULL,  
            p.Sort,  
            p.ID + 1  
    FROM CTE p  
    CROSS APPLY this.nodes('/*/@*') a(x)  
    )  
    SELECT 
            ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,  
            ParentName, ParentPosition,Depth, NodeName, Position,   
            NodeType, FullPath, XPath, TreeView, Value, XMLData 
    FROM CTE2

Convert XML-Blob to relational table

2 Answers2