January 21, 2017 at 1:19 pm
I have the following:
1) @DataDocument: An XML typed data document
2) A two column table based on the following: Column 1 (XPath) contains specific Element and Attribute based paths from the data document, Column 2 string based values that I would like to populate into the xml typed datadocument at the path specified in column 1.
Is there ANY syntax that I have not discovered yet for XML.Modify that would allow me to take the string value of the XPath column to set the value based on the Data column?
Example: @DataDocument = "<document><order firstname="" /></document>"
Column 1 value: "document/order/@firstname"
Column 2 value: "Robert"
Thanks!
January 22, 2017 at 12:44 am
Are there more than one elements with that name in the XML document?
😎
January 22, 2017 at 1:23 am
Here is one possible solution
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- SAMPLE DATA SET
IF OBJECT_ID(N'dbo.TBL_XML_DOC') IS NOT NULL DROP TABLE dbo.TBL_XML_DOC;
CREATE TABLE dbo.TBL_XML_DOC
(
XD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_DOC_XD_ID PRIMARY KEY CLUSTERED
,XD_DOC XML NOT NULL
,XD_PATH VARCHAR(100) NOT NULL
,XD_VAL VARCHAR(100) NOT NULL
);
INSERT INTO dbo.TBL_XML_DOC(XD_DOC,XD_PATH,XD_VAL)
VALUES ('<document><order firstname="" /></document>','document/order/@firstname','Robert')
;
-- INSPECT THE XML DOC BEFORE THE UPDATE
SELECT
XD.XD_DOC
FROM dbo.TBL_XML_DOC XD
WHERE XD.XD_ID = 1;
-- CONSTRUCT THE MODIFY STATEMENT
DECLARE @UPDXD VARCHAR(MAX) =
(SELECT
REPLACE('
UPDATE XD
SET XD_DOC.modify(''replace value of ({{@XD_PATH}})[1] with sql:column("XD.XD_VAL")'')
FROM dbo.TBL_XML_DOC XD','{{@XD_PATH}}',XD.XD_PATH)
FROM dbo.TBL_XML_DOC XD
WHERE XD.XD_ID = 1
);
-- DO THE UPDATE
EXEC (@UPDXD);
-- CHECK THE RESULT
SELECT
XD.XD_DOC
FROM dbo.TBL_XML_DOC XD
WHERE XD.XD_ID = 1;
The document before update<document>
<order firstname="" />
</document>
The document after update<document>
<order firstname="Robert" />
</document>
January 22, 2017 at 11:45 am
Eirikur Eiriksson - Sunday, January 22, 2017 1:23 AMHere is one possible solution
😎
USE TEEST;
GO
SET NOCOUNT ON;-- SAMPLE DATA SET
IF OBJECT_ID(N'dbo.TBL_XML_DOC') IS NOT NULL DROP TABLE dbo.TBL_XML_DOC;
CREATE TABLE dbo.TBL_XML_DOC
(
XD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_DOC_XD_ID PRIMARY KEY CLUSTERED
,XD_DOC XML NOT NULL
,XD_PATH VARCHAR(100) NOT NULL
,XD_VAL VARCHAR(100) NOT NULL
);INSERT INTO dbo.TBL_XML_DOC(XD_DOC,XD_PATH,XD_VAL)
VALUES ('<document><order firstname="" /></document>','document/order/@firstname','Robert')
;-- INSPECT THE XML DOC BEFORE THE UPDATE
SELECT
XD.XD_DOC
FROM dbo.TBL_XML_DOC XD
WHERE XD.XD_ID = 1;-- CONSTRUCT THE MODIFY STATEMENT
DECLARE @UPDXD VARCHAR(MAX) =
(SELECT
REPLACE('
UPDATE XD
SET XD_DOC.modify(''replace value of ({{@XD_PATH}})[1] with sql:column("XD.XD_VAL")'')
FROM dbo.TBL_XML_DOC XD','{{@XD_PATH}}',XD.XD_PATH)
FROM dbo.TBL_XML_DOC XD
WHERE XD.XD_ID = 1
);-- DO THE UPDATE
EXEC (@UPDXD);-- CHECK THE RESULT
SELECT
XD.XD_DOC
FROM dbo.TBL_XML_DOC XD
WHERE XD.XD_ID = 1;The document before update
<document>
<order firstname="" />
</document>The document after update
<document>
<order firstname="Robert" />
</document>
Eirikur,
That is a work of beauty to me. I had started down the thinking process of writing a string and using EXEC to execute it, but I was running into the usual conflicts of EXEC() creating a new context which kept me from accessing my variables and objects. Your elegant use of permanent tables and the REPLACE() function will fit within my working environment and provide me with some proper audit capabilities. I'm feeling a little silly about not considering making my working tables permanent.
I've adjusted your one table structure into two tables so I can create a loop and step through multiple xml updates in the same data document. My need is to create a mapping utility between 3rd Party XML schemas and my standard ordering XML schema without hand coding every possible 3rd Party sender. I want to simply set up an appropriate mapping between the schemas and let SQL Server fill in my order document.
I'll be posting my modifications to your work for historical completeness.
Thank you so much for saving my weekend!
Rob
January 22, 2017 at 12:04 pm
Hi,
For those who may find this thread interesting, I'll lay out my scenario in more depth and how Eirikur's suggestion to resolve my challenge.
I manage a website that accepts order requests from 3rd Party vendors. Each vendor will have a variety of ways that they push their data to me in XML format. I have a standard XML format that I developed more than 10 years ago around which has developed quite a set of automated functionality.
With some new integrations which were currently happening, I decided that I wanted to stop hand coding each vendor's mappings from their data to my data. Some time ago I had found Jacob Sebastian's XMLTable function which beautifully converts any of my XML Documents into tabular data. Now I wanted to take that tabular format and map the various data items into my own XML document structure.
Eirikur's use of the EXEC() command had occurred to me, but like many first attempts, I went directly at the problem trying to get EXEC to work within the current context so I could use my local variables, etc. That of course is not supported. I failed to take into account that all permanent objects are in a "global" context so if I simply stored my work there I could access it. Eirikur showed me the way.
I've modified the recommended solution to accommodate for any number of mappings that I have to make now or in the future. By storing the order document separately from the paths, I can step through each path and apply the change to the same document in sequential updates. This will provide me all the resources I need to bring on any future integrator by simply taking their sample data and mapping their structure to my own. I will not have to produce any more custom vendor specific solutions. That is a Win-Win for me!IF OBJECT_ID(N'dbo.TBL_XML_PATHS') IS NOT NULL DROP TABLE dbo.TBL_XML_PATHS;
CREATE TABLE dbo.TBL_XML_PATHS
(
XD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_DOC_XD_ID PRIMARY KEY CLUSTERED
,XD_ORDER UNIQUEIDENTIFIER NOT NULL
,XD_PATH VARCHAR(100) NOT NULL
,XD_VAL VARCHAR(100) NOT NULL
);
IF OBJECT_ID(N'dbo.TBL_XML_DATA') IS NOT NULL DROP TABLE dbo.TBL_XML_DATA;
CREATE TABLE dbo.TBL_XML_DATA
(
XD_ORDER UNIQUEIDENTIFIER NOT NULL
,XD_DOC xml
);
INSERT INTO dbo.TBL_XML_PATHS(XD_ORDER,XD_PATH,XD_VAL)
VALUES ('00000000-0000-0000-0000-000000000000','document[1]/order[1]/@firstname','Robert')
,('00000000-0000-0000-0000-000000000000','document[1]/order[1]/@lastname','Ruder')
;
INSERT INTO dbo.TBL_XML_DATA(XD_ORDER,XD_DOC)
VALUES ('00000000-0000-0000-0000-000000000000','<document><order firstname="" lastname="" /></document>')
;
-- INSPECT THE XML DOC BEFORE THE UPDATE
SELECT
XP.*, XD.XD_DOC
FROM dbo.TBL_XML_PATHS XP INNER JOIN dbo.TBL_XML_DATA AS XD ON XP.XD_ORDER = XD.XD_ORDER;
DECLARE @UPDXD VARCHAR(MAX);
DECLARE @Incr INT=0;
WHILE (@Incr <= (SELECT MAX(XD_ID) FROM dbo.TBL_XML_PATHS))
BEGIN
-- CONSTRUCT THE MODIFY STATEMENT
SET @Incr = @Incr + 1;
SET @updxd = ( SELECT REPLACE('UPDATE XD SET XD_DOC.modify(''replace value of ({{@XD_PATH}})[1] with sql:column("XP.XD_VAL")'') FROM dbo.TBL_XML_PATHS XP INNER JOIN dbo.TBL_XML_DATA AS XD ON XP.XD_ORDER = XD.XD_ORDER','{{@XD_PATH}}', XP.XD_PATH)
FROM dbo.TBL_XML_PATHS XP
WHERE XP.XD_ID = @Incr
);
-- DO THE UPDATE
EXEC (@UPDXD);
END
-- CHECK THE RESULT
SELECT XD.*
FROM TBL_XML_DATA XD
January 23, 2017 at 2:45 am
You are very welcome Rob and thanks for the feedback.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply