December 29, 2016 at 10:21 am
I think this is the last question on this huge project I've been working on.
I'd like to add the XML column tblDataPermit.XmlDataField to my query but just pull out the value for one node, such as the value for Name.
Here is a sample of the Xml data field:
--<XmlDataField xmlns="MgmsSchema/Business_Information_Detail_Entry" ApplicationNumber="00000865">
<Name>Name value</Name>
<Address>Text1Size50 value</Address>
<Zip>Text2Size50 value</Zip>
</XmlDataField>--
Sample Query:
SELECT
tblDataPermit.ApplicationNumber
,tblDataPermit.PermitNumber
,tblDataPermit.ExpirationDate
,tblDataPermit.Type
,tblDataPermit.Status
,tblDataPermit.ApplicationType
,tblDataParcel.StreetName
,tblDataParcel.StreetNum
,tblDataParcel.Location
FROM
tblDataPermit
INNER JOIN tblDataParcel
ON tblDataPermit.ParcelID = tblDataParcel.ParcelID
WHERE
tblDataPermit.Type = @Type
AND tblDataPermit.Status = @status
AND tblDataPermit.ExpirationDate <= @ExpirationDate
December 29, 2016 at 10:51 am
This looks like your query before, but without the OR. Not sure if it should be there, but I have ommited it.
I haven't tested this mind, as I don't have any sample data.
WITH XMLNAMESPACES(DEFAULT 'MgmsSchema/Business_Information_Detail_Entry')
SELECT DPe.ApplicationNumber,
DPe.PermitNumber,
DPe.ExpirationDate,
DPe.[Type],
DPe.[Status],
DPe.ApplicationType,
DPa.StreetName,
DPa.StreetNum,
DPa.[Location],
p.value('Name[1]', 'VARCHAR(8000)') AS [Name]
FROM tblDataPermit DPe
INNER JOIN tblDataParcel DPa ON DPe.ParcelID = DPa.ParcelID
CROSS APPLY DPe.XmlDataField.nodes('/XmlDataField') t(p)
WHERE DPe.[Type] = @Type
AND DPe.Status = @status
AND DPe.ExpirationDate <= @ExpirationDate;
Edit:
Tested by creating my own DDL and DLM (you should really include these when asking something a little bit more complex than how to use an OR statement 😉 ). Which returns 'Jonathan' for the field [Name].
USE TestDB;
GO
CREATE TABLE DataParcel (ParcelID INT IDENTITY(1,1),
StreetName VARCHAR(50),
StreetNum VARCHAR(10),
[Location] VARCHAR(50));
GO
INSERT INTO DataParcel (StreetNum, StreetName, [Location])
VALUES ('13','Long Road', 'Small City'),
('Flat 7', 'Turn Street', 'Big Town');
GO
CREATE TABLE DataPermit (PermitID INT IDENTITY(1,1),
ParcelID INT,
ApplicationNumber INT,
ApplicationType VARCHAR(50),
PermitNumber INT,
ExpirationDate DATE,
[Type] CHAR(1),
[Status] VARCHAR(15),
XmlDataField XML);
GO
INSERT INTO DataPermit (ParcelID, ApplicationNumber, ApplicationType, PermitNumber, ExpirationDate, [Type], [Status], XmlDataField)
VALUES (1, 7, 'AppHappy', 215145, '30-Dec-2016', 'G', 'Pending',
CAST(
'<XmlDataField xmlns="MgmsSchema/Business_Information_Detail_Entry" ApplicationNumber="00000865">
<Name>Steve</Name>
<Address>514 Little Lane, Village</Address>
<Zip>12345</Zip>
</XmlDataField>' AS XML)),
(2, 15, 'Twitter', 5198418, '31-Dec-2016', 'L', 'Despatched',
CAST(
'<XmlDataField xmlns="MgmsSchema/Business_Information_Detail_Entry" ApplicationNumber="00000865">
<Name>Jonathan</Name>
<Address>16A Floppy Road, Big Little City</Address>
<Zip>51867</Zip>
</XmlDataField>' AS XML));
GO
DECLARE @Type CHAR(1),
@status VARCHAR(10),
@ExpirationDate DATE;
SET @Type = 'L';
SET @status = 'Despatched';
Set @ExpirationDate = '31-Dec-2016';
WITH XMLNAMESPACES(DEFAULT 'MgmsSchema/Business_Information_Detail_Entry')
SELECT DPe.ApplicationNumber,
DPe.PermitNumber,
DPe.ExpirationDate,
DPe.[Type],
DPe.[Status],
DPe.ApplicationType,
DPa.StreetName,
DPa.StreetNum,
DPa.[Location],
p.value('Name[1]', 'VARCHAR(8000)') AS [Name]
FROM DataPermit DPe
INNER JOIN DataParcel DPa ON DPe.ParcelID = DPa.ParcelID
CROSS APPLY DPe.XmlDataField.nodes('/XmlDataField') t(p)
WHERE DPe.[Type] = @Type
AND DPe.Status = @status
AND DPe.ExpirationDate <= @ExpirationDate;
GO
DROP TABLE DataParcel;
DROP TABLE DataPermit;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 29, 2016 at 1:24 pm
I'll plug you code in and let you know. Thank you (I'll try to get better at posting in the future).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply