January 30, 2023 at 3:18 pm
Hello everyone. I don't post here often even though I am constantly on the forums reading articles and such. I was wondering if anyone who is an excellent TSQL coder and perhaps is bored can assist me.
I authored the below code, and it works just fine, but it is ugly (at least I think so). coding for parsing of XML is a weakness of mine.. always has been, and I hated that chapter in the MSSQL certifications (haha), but I can do it, albeit in an ugly manner.
I know there has to be a more elegant way of rewriting this code than what my brain has come up with. I need the results in horizontal format rather than vertical format... hence the dynamic TSQL towards the bottom that is bringing back one row of information rather than the commented out code which will bring back 3 rows of information.
You'll also notice in the code that I am going out and looking at the sys.columns of the table in order to get the proper datatype when I construct my SQLXML.data.value select clause items. I think there is a way when I write the XML to save the sql datatype in the XML, but I haven't got that far. For now, I am simply getting it from the schema.
Anyway, if anyone has any spare time, I would greatly appreciate a more elegant way to do this in the future. and thank you for any assistance you can give. It would be nice if I did not need dynamic sql to accomplish this.
DECLARE @SampleXML XML = '<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Row>
<ExtPmtSchedID>1734603</ExtPmtSchedID>
<NextPmtDt>2023-02-10T07:00:00</NextPmtDt>
<ProcessingStatus xsi:nil="true" />
</Row>
</root>'
DECLARE @statement VARCHAR(MAX) = ''
;WITH CTE (XMLData) AS( SELECT @SampleXML
),
XMLValues AS(
SELECT
--data.value('local-name(.)', 'varchar(100)') AS NodeName,
--data.value('.[not(@xsi:nil = "true")]', 'varchar(100)') AS Value,
--CASE sc.DataType WHEN 'VARCHAR' THEN 'VARCHAR('+CAST(sc.max_length AS VARCHAR(4))+')' ELSE UPPER(sc.DataType) END AS SQLDataType,
'SQLXML.data.value(''' + sc.ColumnName + '[1][not(@xsi:nil = "true")]'', ''' + CASE sc.DataType WHEN 'VARCHAR' THEN 'VARCHAR('+CAST(sc.max_length AS VARCHAR(4))+')' ELSE UPPER(sc.DataType) END + ''') AS [' + sc.ColumnName + ']' AS SelectClause
FROM CTE
CROSS APPLY XMLData.nodes('/root/Row/*') as SQLXML(data)
CROSS APPLY (SELECT DISTINCT sc.name AS ColumnName, sc.column_id, st.name AS DataType, sc.max_length, sc.precision, sc.scale FROM sys.columns sc INNER JOIN sys.systypes st ON st.xtype = sc.system_type_id WHERE OBJECT_NAME(sc.object_id) = 'ExtPmtScheduling' AND sc.name = (data.value('local-name(.)', 'varchar(100)')) AND st.type != 0) sc
)
SELECT @Statement = ';WITH CTE (XMLData) AS ( SELECT CAST(''' + CAST(@SampleXML AS VARCHAR(MAX)) + '''AS XML)
)
SELECT
' + STUFF((SELECT ',' + SelectClause FROM XMLValues FOR XML PATH('')), 1, 1, '') + '
FROM CTE
CROSS APPLY XMLData.nodes(''/root/*'') as SQLXML(data)'
FROM XMLValues
PRINT @statement
EXEC(@statement)
January 30, 2023 at 3:24 pm
Unfortunately, I can't run the above, it initially gives an error about the scalar variable @MLID not being declared (more on that later), and then if I do define it, @statement ends up with the value NULL. This is because we don't have the definition of your table ExtPmtScheduling. It would be good if you could give us a full working example here, with the definition of your table(s), some sample data and expected results.
On your variable @MLID, why are you injecting this into the dynamic statement? You should most certainly be parametrising it. Due to your choice of using the syntax (EXEC(@SQL)), however, then that is impossible. You need to be using sys.sp_executesql and pass the variable as a parameter. In very simple terms:
DECLARE @MyVariable int = 1;
EXEC sys.sp_executesql N'SELECT @MyVariable AS I;', N'@MyVariable int', @MyVariable;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 30, 2023 at 3:24 pm
Yeah, I caught it too and fixed it.
The output of the query is
ExtPmtSchedID NextPmtDt ProcessingStatus
1734603 2023-02-10 07:00:00.000 NULL
The table is ExtPmtScheduling
CREATE TABLE ExtPmtScheduling (ExtPmtSchedId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, NextPmtDt DATETIME, ProcessingStatus INT)
If I can remove the dynamic SQL.. then using sp_executesql will not be necessary at that point. I'm just wondering if I can somehow get the horizontal format I am looking for without using the dynamic SQL at all.
January 30, 2023 at 4:23 pm
No, the XML can be for different columns of different tables…, But generally in the same format with the same root and row tags
January 30, 2023 at 4:26 pm
So you're trying to dynamically get the columns from the XML and (presumably) dynamically insert that into the appropriate columns? Then you are going to need dynamic SQL; you can't create a non-dynamic statement that doesn't have a fixed definition.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 30, 2023 at 4:35 pm
ok.. that is what I was afraid of. I have wracked my brain trying to think of another way to write it that did not involve dynamic SQL,
FYI.. here is an example of another XML that uses the same code but will update/insert into another table where the table name is CustAddress (AddrNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, AddressTypeCd CHAR(1) NOT NULL)
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Row>
<AddrNum>35349271</AddrNum>
<AddressTypeCd>C</AddressTypeCd>
</Row>
</root>
Basically, I am logging the old versus the new values as XML into an XML datatype column. I have a unique BatchId that can be input into the @UndoChanges parameter of the proc to undo an entire batch of changes to the table.
In any case, I will implement the @MLID into sp_executesql proc. Thank you for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply