Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need assistance with XML Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2014 1:29 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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
Post #1559679
Posted Tuesday, April 8, 2014 11:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:04 PM
Points: 1,538, Visits: 4,259
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 APPLY TXML.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
FROM ROOT_NODE RONO
/* 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

Post #1559786
Posted Wednesday, April 9, 2014 8:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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

Post #1559999
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse