Parsing XML file with inline schema using OPENXML in SQL Server

Question

I am trying to extract data from XML file and store in to SQL table using OPENXML in SQL Server. But, the query returns nothing.

XML data

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd"/>
    <xsd:element name="ogridroles">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="ogrid_cde" type="sqltypes:int" nillable="1"/>
                <xsd:element name="role" nillable="1">
                    <xsd:simpleType>
                        <xsd:restriction base="sqltypes:char" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                            <xsd:maxLength value="1"/>
                        </xsd:restriction>
                    </xsd:simpleType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>
<ogridroles xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <ogrid_cde>28</ogrid_cde>
    <role>T</role>
</ogridroles>
<ogridroles xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <ogrid_cde>75</ogrid_cde>
    <role>T</role>
</ogridroles>
<ogridroles xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <ogrid_cde>93</ogrid_cde>
    <role>O</role>
</ogridroles>
<ogridroles xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <ogrid_cde>135</ogrid_cde>
    <role>O</role>
</ogridroles>
</root>

SQL query

DECLARE @xmlStr xml;
DECLARE @idoc INT

SELECT @xmlStr = BulkColumn FROM OPENROWSET(
   BULK 'D:\ogridroles.xml',
   SINGLE_BLOB) AS DATA;

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlStr;
SELECT *
FROM OPENXML (@idoc, '/root/ogridroles',2)
    WITH (
        ogrid_cde   int 'ogrid_cde',
        role varchar(1) 'role')
EXEC sp_xml_removedocument @idoc

I want to extract all row values of the elements

ogrid_cde
role

Please note that SQL Server has a limitation in XML datatype. it is 2GB limit. So, XML file size must be less than 2GB. — user10987050, Feb 01 '19 at 00:03

score 1 · Answer 1 · answered Jan 30 '19 at 00:52

1

I'm not very good with schemas and use another way to decode XMLs:

DECLARE @x XML = 'bla bla bla'
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT ,@x 

SELECT * INTO #t
FROM OPENXML (@idoc, '/',2)
EXEC sp_xml_removedocument @idoc 

SELECT [ogrid_cde] = t3.text, [role] = t4.text
FROM #t as t1
INNER JOIN #t as t2 ON t1.parentid = t2.parentid
INNER JOIN #t as t3 ON t1.id = t3.parentid
INNER JOIN #t as t4 ON t2.id = t4.parentid
WHERE t1.localname = 'ogrid_cde' and t2.localname = 'role'

answered Jan 30 '19 at 00:52

Slava Murygin

1,951
1
10
10

yeah, it works like a very old and very heavy hammer – Shnugo Jan 30 '19 at 15:28
Thanks Slava. Even though it is outdated. openxml is not deprecated yet. your code helps. – user10987050 Jan 30 '19 at 16:00

Shnugo · Accepted Answer · 2019-01-30T11:02:56.890

The approach via FROM OPENXML (together with the procedures to prepare and to remove a document) is out-dated and should not be used anymore.

Much better are the native methods of the XML-type. Try this:

Your XML

DECLARE @xml XML=
N'<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="ogridroles">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="ogrid_cde" type="sqltypes:int" nillable="1" />
          <xsd:element name="role" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:char" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="1" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <ogridroles xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <ogrid_cde>28</ogrid_cde>
    <role>T</role>
  </ogridroles>
  <ogridroles xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <ogrid_cde>75</ogrid_cde>
    <role>T</role>
  </ogridroles>
  <ogridroles xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <ogrid_cde>93</ogrid_cde>
    <role>O</role>
  </ogridroles>
  <ogridroles xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <ogrid_cde>135</ogrid_cde>
    <role>O</role>
  </ogridroles>
</root>';

--The query

WITH XMLNAMESPACES(DEFAULT 'urn:schemas-microsoft-com:sql:SqlRowSet1')
SELECT OgrRol.value('(ogrid_cde/text())[1]','int') AS ogrid_cde
      ,OgrRol.value('(role/text())[1]','nvarchar(10)') AS [role]
FROM @xml.nodes('/*:root/ogridroles') A(ogrRol);

The result

ogrid_cde   role
28          T
75          T
93          O
135         O

Short explanation

You can just ignore the XML-schema here. This would be useful in order to check the data integrity and validity. But assuming the data is correct, we can just read it.

There is a (repeatedly defined) default namespace. But - this is important to see! - the element <root> itself is not living in this namespace!.

Easy going, I've just used a namespace wildcard to address this element with *:root. In general, it's better to avoid wildcards, but in this case this seems acceptable.

We use .nodes() to get a derived table of all the <ogridroles> elements. Then we us .value() to read the text() nodes of the code and the role element.

Hope this helps, happy coding!

I have a new issue with the size of the xml file limitation to 2GB in SQL server. But, I want to load a file of size 35GB . is there any what to load larger xml data to sql server?\ — user10987050, Feb 01 '19 at 18:05
@user10987050 please avoid *follow-up* questions and start a new question. It's a principle on SO to have one question per issue. This makes it easier for followers to use SO as research database. — Shnugo, Feb 01 '19 at 18:25
This is the link for new question [link] https://stackoverflow.com/questions/54472159/parse-large-xml-file-of-size-35-gb-to-transfer-data-to-sql-server-without-out-me?noredirect=1#comment95758309_54472159 — user10987050, Feb 03 '19 at 21:52
I have to disagree on the "Much better" part of this answer. Processing larger files (20mb for example) took the native methods 45 minutes. OpenXML took 4 seconds. — Jon Koeter, Apr 24 '19 at 11:21
@JonKoeter Well, this depends on your needs. If you get a XML from somewhere and you push it into the native XML type, the string-based container must be parsed. Internally everything is stored as a hierarchy table. From now on the native methods are really fast. On the other side, `OpenXML` will have to parse the XML on every call from scratch. Reading rather simple string-based XML as a one-time-action might be something for `OpenXML`. Reading XML in a set-based manner cannot be achieved by `OpenXML`... Furthermore, your test case for the native methods might have been not optimal... — Shnugo, Apr 24 '19 at 11:49

Parsing XML file with inline schema using OPENXML in SQL Server

2 Answers2

Short explanation