April 8, 2014 at 1:29 pm
Hello all,
I have a table which contains an XML column. This column is set up as "<properties><propertyName1>Value1</propertyName1><propertyName2>Value2</propertyName2>" etc...
I also have two temporary tables. One table stores the names of the columns, and the other stores the value for those columns.
What I need to do is update or insert properties into this XML document, based on the records in the two temporary tables. Basically, any properties which exist in the XML document should be updated, and the rest should be inserted.
I know that I can use XML.insert and XML.modify, but I'm not really too familiar with how they work. I did read up a bit on it, and I *think* I understand how I would write an insert statement - but I'm not sure how I would determine whether the records need to be inserted, or updated.
For reference, here's the specifications:
CREATE TABLE #ColumnNames
(
ItemNumber INT,
ItemValue VARCHAR(200)
)
CREATE TABLE #ColumnValues
(
ItemNumber INT,
ItemValue VARCHAR(500)
)
CREATE TABLE #XML
(
XMLDocument XML
)
INSERT INTO #XML (XMLDocument) VALUES ('<properties>
<Prop1>ABC</Prop1>
<Prop2>1</Prop2>
<Prop3>2014-01-01</Prop3>
<Prop4>52.12</Prop4>
</properties>')
INSERT INTO #ColumnNames (ItemNumber, ItemValue)
VALUES (1, 'Prop1')
INSERT INTO #ColumnNames (ItemNumber, ItemValue)
VALUES (2, 'Prop2')
INSERT INTO #ColumnNames (ItemNumber, ItemValue)
VALUES (3, 'Prop5')
INSERT INTO #ColumnNames (ItemNumber, ItemValue)
VALUES (4, 'Prop6')
INSERT INTO #ColumnValues (ItemNumber, ItemValue)
VALUES (1, 'DEF')
INSERT INTO #ColumnValues (ItemNumber, ItemValue)
VALUES (2, '15')
INSERT INTO #ColumnValues (ItemNumber, ItemValue)
VALUES (3, '123-456-122')
INSERT INTO #ColumnValues (ItemNumber, ItemValue)
VALUES (4, 'd2d2s3')
And for reference, here is how I create a new record
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'INSERT INTO #XML (XMLDocument) VALUES (''<properties>'
SELECT
@SQL = @SQL + '<' + cn.ItemValue + '>' + cv.ItemValue + '</' + cn.ItemValue + '>'
FROM #ColumnNames cn
JOIN #ColumnValues cv ON cv.ItemNumber = cn.ItemNumber
WHERE cn.ItemValue NOT LIKE 's_%'
SET @SQL = @SQL + '</properties>'')'
EXEC (@SQL)
Note that the code is sanitized before reaching this stage, so SQL injection is not an issue
April 8, 2014 at 11:18 pm
Slightly twisted but works 😎
CREATE TABLE #ColumnNames
(
ItemNumber INT,
ItemValue VARCHAR(200)
)
CREATE TABLE #ColumnValues
(
ItemNumber INT,
ItemValue VARCHAR(500)
)
CREATE TABLE #XML
(
XMLDocument XML
)
INSERT INTO #XML (XMLDocument) VALUES ('<properties>
<Prop1>ABC</Prop1>
<Prop2>1</Prop2>
<Prop3>2014-01-01</Prop3>
<Prop4>52.12</Prop4>
</properties>')
INSERT INTO #ColumnNames (ItemNumber, ItemValue)
VALUES (1, 'Prop1')
INSERT INTO #ColumnNames (ItemNumber, ItemValue)
VALUES (2, 'Prop2')
INSERT INTO #ColumnNames (ItemNumber, ItemValue)
VALUES (3, 'Prop5')
INSERT INTO #ColumnNames (ItemNumber, ItemValue)
VALUES (4, 'Prop6')
INSERT INTO #ColumnValues (ItemNumber, ItemValue)
VALUES (1, 'DEF')
INSERT INTO #ColumnValues (ItemNumber, ItemValue)
VALUES (2, '15')
INSERT INTO #ColumnValues (ItemNumber, ItemValue)
VALUES (3, '123-456-122')
INSERT INTO #ColumnValues (ItemNumber, ItemValue)
VALUES (4, 'd2d2s3')
DECLARE @SQL_STR NVARCHAR(MAX) = N''
;WITH ROOT_NODE AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY (SELECT NULL) ORDER BY (SELECT NULL)) AS ROOTRID
,ROOT.NODE.value('local-name(.)','NVARCHAR(MAX)') AS RootName
,ROOT.NODE.query('*') AS ANCOR
FROM #XML TXML
/* ROOT */
CROSS APPLYTXML.XMLdocument.nodes('
/*') AS ROOT(NODE)
)
,LEVEL_1 AS
(
SELECT
RONO.ROOTRID
,ROW_NUMBER() OVER (PARTITION BY RONO.ROOTRID ORDER BY (SELECT NULL)) AS LEVEL_1_RID
,RONO.RootName
,LEVEL1.NODE.value('local-name(.)','NVARCHAR(MAX)') AS LEVEL_1_Name
,LEVEL1.NODE.value('.[1]','NVARCHAR(MAX)') AS LEVEL_1_VALUE
FROMROOT_NODERONO
/* LEVEL 1 */
CROSS APPLY RONO.ANCOR.nodes('
*') AS LEVEL1(NODE)
)
SELECT @SQL_STR = (
SELECT
'UPDATE #XML SET XMLdocument.modify(''' +
CASE
WHEN L1.LEVEL_1_Name IS NOT NULL THEN 'replace value of (/properties/' + CN.ItemValue + '[1]/text())[1] with "' + CV.ItemValue + '"'');'
WHEN L1.LEVEL_1_Name IS NULL THEN 'insert <' + CN.ItemValue + '>' + CV.ItemValue + '</' + CN.ItemValue + '> as last into (/properties)[1]'');'
END AS ACTION_STR
FROM #ColumnNames CN
INNER JOIN #ColumnValues CV
ON CN.ItemNumber = CV.ItemNumber
LEFT OUTER JOIN LEVEL_1 L1
ON CN.ItemValue = L1.LEVEL_1_Name
WHERE CV.ItemValue <> L1.LEVEL_1_VALUE
OR L1.LEVEL_1_Name IS NULL
ORDER BY CN.ItemValue
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)')
EXEC sp_executesql @SQL_STR;
SELECT * FROM #XML
drop table #ColumnNames
drop table #ColumnValues
drop table #XML
April 9, 2014 at 8:48 am
Nice solution =)
But, when dissecting it, I realise that what you've done is constructed a series of UPDATE statements for each property that needs updating and inserting.
So, taking inspiration from the idea that you've presented,
DECLARE @SQL_STR NVARCHAR(MAX) = '<properties>'
DECLARE @Document XML
SELECT @Document = XMLDocument
FROM #XML
;
WITH cte AS
(
SELECT
LEVEL1.NODE.value('local-name(.)','NVARCHAR(MAX)') AS LEVEL_1_Name,
LEVEL1.NODE.value('.[1]','NVARCHAR(MAX)') AS LEVEL_1_VALUE
FROM @document.nodes('/properties/*') AS LEVEL1(NODE)
)
SELECT
@SQL_STR = @SQL_STR +
(
CASE
WHEN CN.ItemValue IS NULL THEN '<' + LEVEL_1_Name + '>' + LEVEL_1_VALUE + '</' + LEVEL_1_Name + '>'
ELSE '<' + CN.ItemValue + '>' + CV.ItemValue + '</' + CN.ItemValue + '>'
END
)
FROM #ColumnNames CN
JOIN #ColumnValues CV ON CN.ItemNumber = CV.ItemNumber
FULL JOIN cte L1 ON CN.ItemValue = L1.LEVEL_1_Name
SET @SQL_STR = @SQL_STR + '</properties>'
SET @SQL_STR = 'UPDATE #XML SET XMLDocument = ''' + @SQL_STR + ''''
EXEC sp_ExecuteSQL @SQL_STR
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply