Updating an attribute using xquery in SQL Server 2008 R2
I have a table that defines the layout of a form in XML. It has <Control ...>
nodes that have attributes like Id (GUID) and DataType (char), etc.
This XML data is used to create the form at run time, where the data is stored in XML in named elements <Field ...>
that have attributes: Name (matches the GUID in the node control), and Data (contains the value entered into the control).
We have an issue where when you use .ToString on a Date object it uses machine parameters for the date format. Thus, dates can be stored in any possible format. I updated the code to always keep dates in the format YYYY/MM/DD
, but now I need to update the existing data in the database to YYYY/MM/DD
. Since we have no way of knowing what format the date was saved in, we'll just assume it was saved as MM/DD/YYYY
.
So now my problem is trying to update XML in SQL Server. Using xquery and the CROSS APPLY.nodes () stuff, I can write a query to find all the incorrectly formatted dates, but I can't figure out how to update them.
This SQL script will create table variables and populate them with some test data and has a query that will return include date values. the last part that is commented out is how I tried to update them to the new format YYYY/MM/DD
, but if you uncomment it you can see that it doesn't work.
Does anyone have any idea?
PS This can only be done in a SQL script. I know it would be very easy to write a function in .NET that reads in all xml data and updates the attributes and then saves the data back to the database, but that is not possible in this case because: reasons.
DECLARE @Form AS Table
(
FormID INT,
FormDataXML XML
);
DECLARE @ScreenData AS Table
(
ScreenDataID INT,
FormID INT,
ScreenDataXML XML
);
DECLARE @ControlsToUpdate AS TABLE
(
FormID INT,
ControlID char(36),
DataType CHAR(1)
);
INSERT INTO @Form
VALUES (1, '<Form><Control Id="00000000-0000-0000-0000-000000000000" DataType="D" /><Control Id="00000000-0000-0000-0000-000000000001" DataType="N" /></Form>');
INSERT INTO @Form
VALUES (2, '<Form><Control Id="00000000-0000-0000-0000-000000000002" DataType="D" /><Control Id="00000000-0000-0000-0000-000000000003" DataType="D" /></Form>');
INSERT INTO @ScreenData
VALUES (1, 1, '<ScreenData><Field Name="00000000-0000-0000-0000-000000000000" Data="01/31/2012" /><Field Name="00000000-0000-0000-0000-000000000001" Data="1234.56" /></ScreenData>');
INSERT INTO @ScreenData
VALUES (2, 1, '<ScreenData><Field Name="00000000-0000-0000-0000-000000000000" Data="02/28/2013" /><Field Name="00000000-0000-0000-0000-000000000001" Data="0" /></ScreenData>');
INSERT INTO @ScreenData
VALUES (3, 2, '<ScreenData><Field Name="00000000-0000-0000-0000-000000000002" Data="03/31/2013" /><Field Name="00000000-0000-0000-0000-000000000003" Data="04/30/2013" /></ScreenData>');
INSERT INTO @ScreenData
VALUES (4, 2, '<ScreenData><Field Name="00000000-0000-0000-0000-000000000002" Data="2013/05/31" /><Field Name="00000000-0000-0000-0000-000000000003" Data="2013/06/30" /></ScreenData>');
--Data treated as scheduled items
INSERT INTO @ScreenData
VALUES (5, 2, '<ScreenData><Item><Field Name="00000000-0000-0000-0000-000000000002" Data="01/01/2012" /><Field Name="00000000-0000-0000-0000-000000000003" Data="02/02/2012" /></Item><Item><Field Name="00000000-0000-0000-0000-000000000002" Data="03/03/2012" /><Field Name="00000000-0000-0000-0000-000000000003" Data="04/04/2012" /></Item></ScreenData>')
INSERT INTO @ControlsToUpdate
SELECT FormID,
data.control.value('@Id', 'char(36)'),
data.control.value('@DataType', 'char(1)')
FROM @Form
CROSS APPLY FormDataXML.nodes('//Control') data(control)
WHERE data.control.value('@DataType', 'char(1)') = 'D';
--This will display the ScreenDataID, FormID, ControlID, and current Date value for dates that are in the old mm/dd/yyyy format
SELECT d.ScreenDataID, d.FormID, c.ControlID,
data.field.value('@Data', 'char(10)') as Date
FROM @ScreenData d
CROSS APPLY d.ScreenDataXML.nodes('//Field') as data(field)
INNER JOIN @ControlsToUpdate c ON c.ControlID = data.field.value('@Name', 'CHAR(36)')
AND d.FormID = c.FormID
WHERE data.field.value('@Data', 'char(10)')
LIKE '[01][0123456789]/[0123][0123456789]/[12][0123456789][0123456789][0123456789]';
--UPDATE d
--SET data.field.modify('replace value of (/@Data) with "' +
-- SUBSTRING(data.field.value('@Data', 'char(10)'), 7, 4) + '/' +
-- SUBSTRING(data.field.value('@Data', 'char(10)'), 1, 2) + '/' +
-- SUBSTRING(data.field.value('@Data', 'char(10)'), 4, 2) + '"')
--FROM @ScreenData d
--CROSS APPLY d.ScreenDataXML.nodes('//Field') as data(field)
--INNER JOIN @ControlsToUpdate c ON c.Id = data.field.value('@Name', 'CHAR(36)')
-- AND d.FormID = c.FormID
--WHERE data.field.value('@Data', 'varchar(MAX)')
--LIKE '[01][0123456789]/[0123][0123456789]/[12][0123456789][0123456789][0123456789]';
GO
- Edit - The Xml in @ScreenData can also contain nodes <Item>
containing nodes <Field>
. When this happens, you will have multiple nodes <Field>
that have the same value for the Name attribute. This is the case when there is a list of items on the screen, you will have several items, each of which refers to one control, but has its own values. ScreenDataID 5 shows this.
source to share
I will not show you exactly what you want. I'll show you how to change all your dates to yyyy-mm-dd
, since that's what they need to be in XML.
You have figured out so far that I don't think you will have problems changing the code below if you decide to use a different date format.
You cannot update more than one value at a time in XML, so the update must be looped over the forms and fields that need to be updated.
SET DATEFORMAT ymd;
needed to convert from string to date to work both with yyyy/mm/dd
and mm/yy/dd
at the same time.
SET DATEFORMAT ymd;
DECLARE @FormID INT;
DECLARE @FieldID UNIQUEIDENTIFIER;
DECLARE @I INT;
DECLARE ControlsToUpdate CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT F.FormID,
T.N.value('@Id', 'uniqueidentifier') AS FieldID
FROM @Form AS F
CROSS APPLY F.FormDataXML.nodes('/Form/Control') AS T(N)
WHERE T.N.value('@DataType', 'char(1)') = 'D';
OPEN ControlsToUpdate;
FETCH NEXT FROM ControlsToUpdate
INTO @FormID, @FieldID;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @I = MAX(S.ScreenDataXML.value('count(//Field[@Name = sql:variable("@FieldID")])', 'INT'))
FROM @ScreenData AS S
WHERE S.FormID = @FormID;
WHILE @I > 0
BEGIN
UPDATE S
SET ScreenDataXML.modify('replace value of
((//Field[@Name = sql:variable("@FieldID")]
/@Data)[sql:variable("@I")])[1]
with sql:column("T.D")')
FROM @ScreenData AS S
CROSS APPLY
(SELECT S.ScreenDataXML.value('((//Field[@Name = sql:variable("@FieldID")]
/@Data)[sql:variable("@I")])[1]', 'DATE')) AS T(D)
WHERE S.FormID = @FormID;
SET @I -= 1;
END
FETCH NEXT FROM ControlsToUpdate
INTO @FormID, @FieldID;
END
CLOSE ControlsToUpdate;
source to share