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)
August 25, 2020 at 4:31 pm
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);
August 25, 2020 at 4:35 pm
Note also that your original XML was invalid (the <AC> row was not terminated).
August 25, 2020 at 5:23 pm
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!
August 25, 2020 at 6:03 pm
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
August 26, 2020 at 2:24 pm
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy