CODEWITHSUNDEEP
sql-serverxmlt-sql
MrH
MrH

Reputation: 41

SQL Server XML querying wrong output

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)

Upvotes: 3

Views: 65

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

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

Upvotes: 1

Related Questions