Xpath query with cross apply help - almost got it

Question

Xpath query with cross apply help - almost got it

SSChampion

Points: 10975

August 25, 2020 at 3:55 pm

The node that is used in the cross apply has multiple instances. The OTimes node has 2 children (OTime) each with its own set of values. I'm trying to extract the PNum and StartDate.

My XML

<OTG xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AC>N32</AC
  <CTY>KLR</CTY>
  <OTimes>
    <OTime>
      <BTime>780</BTime>
      <CTime>1000</CTime>
      <EndDate>2020-09-22T08:14:00</EndDate>
      <ID>4f91926f-9e8a-42cd-9168-1a544b70cdc8</ID>
      <IsInit>true</IsInit>
      <OverC>
        <string>ABC</string>
        <string>DEF</string>
        <string>GHI</string>
      </OverC>
      <PNum>X4507A</PNum>
      <StartDate>2020-09-19T13:35:00</StartDate>
    </OTime>
    <OTime>
      <BTime>892</BTime>
      <CTime>1000</CTime>
      <EndDate>2020-09-29T08:15:00</EndDate>
      <ID>184cb7c5-907a-49e1-b42f-b7e736fbe7ff</ID>
      <IsInit>true</IsInit>
      <OverC>
        <string>TUV</string>
        <string>WXY</string>
        <string>ZMN</string>
      </OverC>
      <PNum>X4513B</PNum>
      <StartDate>2020-09-26T13:35:00</StartDate>
    </OTime>
  </OTimes>
  <Pos>LH</Pos>
</OTG>

My Query:

This query gives me the correct number of records, but the [1] index position gives me the first PNum and Start Date for both rows. I need to get the first one for the first row and the second one for the second row. The x(i) dataset has 2 rows but how do i tell it to get the relative values?

SELECT 
    otg.Fld1
,otg.fld2
,x.i.value('(/OTG/OTimes/OTime/PNum)[1]','varchar(7)') 
,x.i.value('(/OTG/OTimes/OTime/StartDate)[1]','date') 
FROM ot.OGSentAudit otg
CROSS APPLY otg.OTimeGroup.nodes('./OTG/OTimes/OTime') AS x(i)

This topic was modified 4 years, 3 months ago by TangoVictor.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246971 More actions · Answer 1

Does this get you closer?

USE Testing;

DROP TABLE IF EXISTS #SomeXML;

CREATE TABLE #SomeXML
(
    OTimeGroup XML NOT NULL
);

INSERT #SomeXML
(
    OTimeGroup
)
SELECT '<OTG xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AC>N32</AC>
  <CTY>KLR</CTY>
  <OTimes>
    <OTime>
      <BTime>780</BTime>
      <CTime>1000</CTime>
      <EndDate>2020-09-22T08:14:00</EndDate>
      <ID>4f91926f-9e8a-42cd-9168-1a544b70cdc8</ID>
      <IsInit>true</IsInit>
      <OverC>
        <string>ABC</string>
        <string>DEF</string>
        <string>GHI</string>
      </OverC>
      <PNum>X4507A</PNum>
      <StartDate>2020-09-19T13:35:00</StartDate>
    </OTime>
    <OTime>
      <BTime>892</BTime>
      <CTime>1000</CTime>
      <EndDate>2020-09-29T08:15:00</EndDate>
      <ID>184cb7c5-907a-49e1-b42f-b7e736fbe7ff</ID>
      <IsInit>true</IsInit>
      <OverC>
        <string>TUV</string>
        <string>WXY</string>
        <string>ZMN</string>
      </OverC>
      <PNum>X4513B</PNum>
      <StartDate>2020-09-26T13:35:00</StartDate>
    </OTime>
  </OTimes>
  <Pos>LH</Pos>
</OTG>';

SELECT PNum = x.value('PNum[1]', 'VARCHAR(50)')
      ,StartDate = x.value('StartDate[1]', 'DateTime')
FROM #SomeXML sx
    CROSS APPLY sx.OTimeGroup.nodes('//*:OTG/*:OTimes/*:OTime') r(x);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246971 More actions · Answer 2

Note also that your original XML was invalid (the <AC> row was not terminated).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

TangoVictor SSChampion Points: 10975 More actions · Answer 3

Thank You Phil!

The key I was missing is using the i as the alias, not the x.i (from my example) or the x not r.x from your example.

That is what solved it, also in the meantime got it to work using this line:

,x.i.query('./StartDate').value('(/StartDate)[1]','date')

But your method is way cleaner, this is starting to make more sense.

I took your example and modified it a bit by adding a couple extra columns and then i changed the node statement to look more like my original one and it still worked. I haven't figured out yet what the //:* notation does but it works both ways.

Here is your example after i modified it:

USE Testing;

DROP TABLE IF EXISTS #SomeXML;

CREATE TABLE #SomeXML
(
Key1 INT
,Key2 VARCHAR(5)
,OTimeGroup XML NOT NULL
);

INSERT #SomeXML
(
 Key1
 ,Key2
 ,OTimeGroup
)
SELECT 1,'cat','<OTG xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AC>N32</AC>
  <CTY>KLR</CTY>
  <OTimes>
    <OTime>
      <BTime>780</BTime>
      <CTime>1000</CTime>
      <EndDate>2020-09-22T08:14:00</EndDate>
      <ID>4f91926f-9e8a-42cd-9168-1a544b70cdc8</ID>
      <IsInit>true</IsInit>
      <OverC>
        <string>ABC</string>
        <string>DEF</string>
        <string>GHI</string>
      </OverC>
      <PNum>X4507A</PNum>
      <StartDate>2020-09-19T13:35:00</StartDate>
    </OTime>
    <OTime>
      <BTime>892</BTime>
      <CTime>1000</CTime>
      <EndDate>2020-09-29T08:15:00</EndDate>
      <ID>184cb7c5-907a-49e1-b42f-b7e736fbe7ff</ID>
      <IsInit>true</IsInit>
      <OverC>
        <string>TUV</string>
        <string>WXY</string>
        <string>ZMN</string>
      </OverC>
      <PNum>X4513B</PNum>
      <StartDate>2020-09-26T13:35:00</StartDate>
    </OTime>
  </OTimes>
  <Pos>LH</Pos>
</OTG>';

SELECT 
sx.Key1
,sx.Key2
,x.value('PNum[1]', 'VARCHAR(50)')
,x.value('StartDate[1]', 'DateTime')
FROM #SomeXML sx
    CROSS APPLY sx.OTimeGroup.nodes('/OTG/OTimes/OTime') r(x);

If you don't mind could you explain how these are different?

CROSS APPLY sx.OTimeGroup.nodes('/OTG/OTimes/OTime') r(x);

and

CROSS APPLY sx.OTimeGroup.nodes('//*:OTG/*:OTimes/*:OTime') r(x);

Also in that xml I posted originally those nodes were all changed manually for the purpose of posting online. That error on closing <AC> tag was just from those changes and not part of my original issue.

Thanks!

Phil Parkin SSC Guru Points: 246971 More actions · Answer 4

Glad you got it working. The use of the // and * wildcards makes the query processor 'look' for matches everywhere in the selected document path, not just on the specific path specified. I can probably come up with some sample data to demonstrate the difference, if you're interested.

For your purposes, the non-wildcard version looks sufficient and is (presumably) faster.

My XPath knowledge is little more than rudimentary, so I won't attempt a more detailed explanation for fear of incriminating myself. Instead, you may find this link informative: https://www.w3schools.com/xml/xpath_syntax.asp

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

TangoVictor SSChampion Points: 10975 More actions · Answer 5

OK thanks Phil, that makes sense I don't need further examples. The XML is pretty straight forward and generally only contains one or 2 OTime elements. It's xml we generate in SSIS and send to a web service. The request is logged to an audit table and needed a tabular report in a hurry so I appreciate the response.

Tom