CODEWITHSUNDEEP
sql-serverxmlt-sql
asmgx
asmgx

Reputation: 8004

How to put a multi level XML in 1 table in SQL Server?

I have an XML file in C:\data.xml

The XML looks like this

<?xml version="1.0" encoding="UTF-8"?>
<diseaseset>
     <diseases source="intuitive">
          <disease name="Asthma">
                <doc judgment="Y" id="3"/>
                <doc judgment="N" id="5"/>
                <doc judgment="N" id="7"/>
                <doc judgment="Y" id="9"/>
          </disease>
          <disease name="CAD">
                <doc judgment="Y" id="3"/>
                <doc judgment="Y" id="4"/>
                <doc judgment="N" id="6"/>
                <doc judgment="N" id="9"/>
          </disease>
    </diseases>
   <diseases source="textual">
          <disease name="Asthma">
                <doc judgment="N" id="1"/>
                <doc judgment="N" id="2"/>
                <doc judgment="N" id="8"/>
                <doc judgment="N" id="9"/>
          </disease>
          <disease name="CAD">
                <doc judgment="N" id="2"/>
                <doc judgment="Y" id="4"/>
                <doc judgment="N" id="5"/>
                <doc judgment="N" id="9"/>
          </disease>
     </diseases>
</diseaseset>

I want to put this in a table in this format

Source      Name    Judgment   ID
---------------------------------
intuitive   Asthma  Y          3
intuitive   Asthma  N          5
intuitive   Asthma  N          7
intuitive   Asthma  Y          9
intuitive   CAD     Y          3
intuitive   CAD     Y          4
intuitive   CAD     N          6
intuitive   CAD     N          9
....

I tried with this query but it did not work

DECLARE @x xml
 
SELECT @x = C
FROM OPENROWSET (BULK 'C:\data.xml', SINGLE_BLOB) AS X(C)
            
DECLARE @hdoc int
    
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT source, name, judgment, id
FROM OPENXML (@hdoc, '/diseaseset/diseases/disease/doc' , 3)
WITH(
    id varchar(100),
    judgment VARCHAR(100)
    name VARCHAR(100)
    source VARCHAR(100)
    )

but the results always give null for name and source columns

how to fix that?

Upvotes: 1

Views: 51

Answers (1)

marc_s
marc_s

Reputation: 754508

Try something like this:

SELECT
    DiseasesSource = xc.value('(../../@source)', 'varchar(50)'),
    DiseasesName = xc.value('(../@name)', 'varchar(50)'),
    Judgement = xc.value('@judgment', 'varchar(50)'),
    Id = xc.value('@id', 'int')
FROM
    @x.nodes('/diseaseset/diseases/disease/doc') AS XT(XC)

I'd strongly recommend using this native, built-in XQuery support over the old, legacy, problem-plagued OpenXml stuff......

You'll get a result something like this:

DiseasesSource DiseasesName Judgement Id
intuitive Asthma Y 3
intuitive Asthma N 5
intuitive Asthma N 7
intuitive Asthma Y 9
intuitive CAD Y 3
intuitive CAD Y 4
intuitive CAD N 6
intuitive CAD N 9
textual Asthma N 1
textual Asthma N 2
textual Asthma N 8
textual Asthma N 9
textual CAD N 2
textual CAD Y 4
textual CAD N 5
textual CAD N 9

Upvotes: 1

Related Questions