DECLARE
@x XMLSET @x = '<CategoryDetails xmlns="https://api.trademe.co.nz/v1"> <CategoryId>DW3085</CategoryId> <Name>Dredging</Name> <Path>/Machinery/Machinery-Attachments/Dredging</Path> </CategoryDetails>' ;WITH xmlnamespaces(default 'https://api.trademe.co.nz/v1')
SELECT @x.query('(/CategoryDetails/CategoryId/node())[1]') example 01
-------------------------------------
treaddepths - COLUMN CONTENTS: <td lf="1605" rf="1605" lr1="1608" rr1="1608" sp="1606" />
SELECT inspectionguid = i.inspectionguid,
partid = 0,
tyredepthleftfront = r.value('@lF', 'int'),
tyredepthrightfront = r.value('@rF', 'int')
FROM inspections i CROSS apply i.treaddepths.nodes('/tD') AS res(r) example 02
-------------------------------------
<arrayofcustomers XMLNS:xsd="https://www.w3.org/2001/XMLSchema" XMLNS:xsi="https://www.w3.org/2001/XMLSchema-instance"> <customer> <itemid>1</itemid> <value>mr smith</value> </customer> <customer> <itemid>2</itemid> <value>mr bloggs</value> </customer> </arrayofcustomers>
SELECT cust.value('(ItemId)[1]', 'int') AS 'ItemID',
cust.value('(Value)[1]', 'Varchar(50)') AS 'Customer Name'
FROM dbo.sales.customerlist.nodes('/ArrayOfCustomers/Customer') AS aoc(cust) example 03
-------------------------------------
<vfs> <vf fid="1896" ds="Power Rear Windows" /> <vf fid="1814" ds="Central Locking" /> <vf fid="1837" ds="Electric Mirrors" /> <vf fid="2023" ds="Steering Wheel Stereo Controls" /> <vf fid="1855" ds="Fog Lamps" /> <vf fid="1765" ds="Dual Airbags" /> <vf fid="1769" ds="ABS (Antilock Brakes)" /> <vf fid="1773" ds="Air Conditioning" /> <vf fid="17609" ds="14" Steel Wheels" /> <vf fid="1890" ds="Park Distance Control" /> <vf fid="1970" ds="Radio CD Player" /> <vf fid="1889" ds="Power Steering" /> <vf fid="1892" ds="Power Front Windows" /> </vfs>SELECT TOP 10 InspectionGUID = i.inspectionguid,
PartID = 0,
vehiclefeatures,
Features = f.value('@ds', 'varchar(255)')
FROM inspections i CROSS apply i.vehiclefeatures.nodes('/vFs/vF') AS Feature(f)
