SQL Server XML querying wrong output

Question

I'm new to XML thing and I have this xml data..

<?xml version="1.0"?>
<OMeS xmlns="pm/cnf_pte_msn.1.0.xsd">
  <PMSetup startTime="2015-10-21T00:00:00.000+00:00:00" interval="60">

    <PMMOResult>
      <MO>
        <baseId>12345</baseId>
        <localMoid>67890</localMoid>
      </MO>
      <NE_1.0 measurementType="S1">
        <M8000C0>1601</M8000C0>
        <M8000C1>1597</M8000C1>
        <M8000C2>4</M8000C2>
        <M8000C3>0</M8000C3>
        <M8000C4>0</M8000C4>
        <M8000C5>0</M8000C5>
      </NE_1.0>
    </PMMOResult>

    <PMMOResult>
      <MO>
        <baseId>678910</baseId>
        <localMoid>109876</localMoid>
      </MO>
      <NE_1.0 measurementType="S1">
        <M8000C0>860</M8000C0>
        <M8000C1>858</M8000C1>
        <M8000C2>2</M8000C2>
        <M8000C3>0</M8000C3>
        <M8000C4>0</M8000C4>
        <M8000C5>0</M8000C5>
      </NE_1.0>
    </PMMOResult>

    <PMMOResult>
      <MO>
        <baseId>111213</baseId>
        <localMoid>131211</localMoid>
      </MO>
      <NE_1.0 measurementType="S1">
        <M8000C0>3533</M8000C0>
        <M8000C1>3504</M8000C1>
        <M8000C2>29</M8000C2>
        <M8000C3>0</M8000C3>
        <M8000C4>0</M8000C4>
        <M8000C5>0</M8000C5>
      </NE_1.0>
    </PMMOResult>

  </PMSetup>
</OMeS>

Now I want query it then insert into table structure like this..

startTime | interval | baseId | localMoid | M8000C0 | M8000C1 | M8000C2 | M8000C3 | M8000C4 | M8000C5

But I get the wrong output. Pls. help! Here's my sample query only code:

;WITH XMLNAMESPACES (DEFAULT 'pm/cnf_pte_msn.1.0.xsd')
SELECT 
Tier1.value('@startTime', 'varchar(50)') as startTime,
Tier1.value('@interval', 'int') as interval,
Tier2.value('baseId[1]', 'varchar(50)') as baseId,
Tier2.value('localMoid[1]', 'varchar(50)') as localMoid,
Tier3.value('M8000C0[1]', 'float') as M8000C0,
Tier3.value('M8000C1[1]', 'float') as M8000C1,
Tier3.value('M8000C2[1]', 'float') as M8000C2,
Tier3.value('M8000C3[1]', 'float') as M8000C3,
Tier3.value('M8000C4[1]', 'float') as M8000C4,
Tier3.value('M8000C5[1]', 'float') as M8000C5
FROM 
@RawXML.nodes('/OMeS/PMSetup') as T1(Tier1)
cross apply @RawXML.nodes('/OMeS/PMSetup/PMMOResult/MO') as T2(Tier2)
cross apply @RawXML.nodes('/OMeS/PMSetup/PMMOResult/NE_1.0') as T3(Tier3)

But I get the wrong output. So show the correct one.

Lukasz Szozda
– Lukasz Szozda

2015-10-21 11:16:14 +00:00
Commented Oct 21, 2015 at 11:16 — Lukasz Szozda
– Lukasz Szozda, Commented Oct 21, 2015 at 11:16

Lukasz Szozda · Accepted Answer · 2015-10-21 12:03:23Z

1

Your question is not clear what correct result is, but I assume you want one row per XML Element <PMMOResult> :

;WITH XMLNAMESPACES (DEFAULT 'pm/cnf_pte_msn.1.0.xsd')
SELECT 
Tier1.value('@startTime', 'varchar(50)') as startTime,
Tier1.value('@interval', 'int') as interval,
Tier2.value('((.)/MO/baseId)[1]', 'varchar(50)') as baseId,
Tier2.value('((.)/MO/localMoid)[1]', 'varchar(50)') as localMoid,
Tier2.value('((.)/NE_1.0/M8000C0)[1]', 'float') as M8000C0,
Tier2.value('((.)/NE_1.0/M8000C1)[1]', 'float') as M8000C1,
Tier2.value('((.)/NE_1.0/M8000C2)[1]', 'float') as M8000C2,
Tier2.value('((.)/NE_1.0/M8000C3)[1]', 'float') as M8000C3,
Tier2.value('((.)/NE_1.0/M8000C4)[1]', 'float') as M8000C4,
Tier2.value('((.)/NE_1.0/M8000C5)[1]', 'float') as M8000C5
FROM  @RawXML.nodes('/OMeS/PMSetup') as T1(Tier1)
cross apply @RawXML.nodes('/OMeS/PMSetup/PMMOResult') as T2(Tier2);

LiveDemo

Output:

╔══════════════════════════════════╦══════════╦════════╦═══════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╗
║            startTime             ║ interval ║ baseId ║ localMoid ║ M8000C0 ║ M8000C1 ║ M8000C2 ║ M8000C3 ║ M8000C4 ║ M8000C5 ║
╠══════════════════════════════════╬══════════╬════════╬═══════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ 2015-10-21T00:00:00.000+00:00:00 ║       60 ║  12345 ║     67890 ║    1601 ║    1597 ║       4 ║       0 ║       0 ║    0    ║
║ 2015-10-21T00:00:00.000+00:00:00 ║       60 ║ 678910 ║    109876 ║     860 ║     858 ║       2 ║       0 ║       0 ║    0    ║
║ 2015-10-21T00:00:00.000+00:00:00 ║       60 ║ 111213 ║    131211 ║    3533 ║    3504 ║      29 ║       0 ║       0 ║    0    ║
╚══════════════════════════════════╩══════════╩════════╩═══════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╝

EDIT:

Even simpler, no need CROSS APPLY:

;WITH XMLNAMESPACES (DEFAULT 'pm/cnf_pte_msn.1.0.xsd')
SELECT 
Tier2.value('../@startTime', 'varchar(50)') as startTime,
Tier2.value('../@interval', 'int') as interval,
Tier2.value('((.)/MO/baseId)[1]', 'varchar(50)') as baseId,
Tier2.value('((.)/MO/localMoid)[1]', 'varchar(50)') as localMoid,
Tier2.value('((.)/NE_1.0/M8000C0)[1]', 'float') as M8000C0,
Tier2.value('((.)/NE_1.0/M8000C1)[1]', 'float') as M8000C1,
Tier2.value('((.)/NE_1.0/M8000C2)[1]', 'float') as M8000C2,
Tier2.value('((.)/NE_1.0/M8000C3)[1]', 'float') as M8000C3,
Tier2.value('((.)/NE_1.0/M8000C4)[1]', 'float') as M8000C4,
Tier2.value('((.)/NE_1.0/M8000C4)[1]', 'float') as M8000C5
FROM @RawXML.nodes('/OMeS/PMSetup/PMMOResult') as T2(Tier2);

LiveDemo

edited Oct 21, 2015 at 12:03

answered Oct 21, 2015 at 11:45

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Lukasz Szozda Over a year ago

@MrH You're welcome. I updated even simpler solution

MrH Over a year ago

Great! Thanks. And much cleaner.

MrH Over a year ago

Hi, using the above code, i try to insert into a table but its really slow, its like 7 mins by just 1 file (~500 KB). i need to import about 10,000 files everyday. Pls. help.

Lukasz Szozda Over a year ago

@MrH Hi ask new question and share code you use to read file. In your demo here you read data froml variable.

MrH Over a year ago

Thanks for reply. Appreciate this. After spending hours researching, i found out that i can index an xml column & also the new features of SQL Server 2012 which is selective xml index. I'm gonna first try this and post some feedback. Thanks.

|

Collectives™ on Stack Overflow

SQL Server XML querying wrong output

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related