Help with OpenXML
-
Below is a snipet of code that I'm using to develop some XML processing. SET @XMLSTRING = '<DATAMATRIX> <FSDATAMATRIX> <DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE> <FIELD1>UNITED STATES</FIELD1><FIELD2>BEVERAGES, ALCOHOLIC</FIELD2><FIELD3>123</FIELD3><FIELD4>FEMA</FIELD4><FIELD5>NO</FIELD5></FSDATAMATRIX></DATAMATRIX>' PRINT @XMLSTRING EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLSTRING SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) EXEC sp_xml_removedocument @idoc The problem I'm having is that I can't seem to get the values of FIELD1, FIELD2, FIELD3 by using the OPENXML procedure. Can someone shed some light on this ? Thanks.
-
Below is a snipet of code that I'm using to develop some XML processing. SET @XMLSTRING = '<DATAMATRIX> <FSDATAMATRIX> <DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE> <FIELD1>UNITED STATES</FIELD1><FIELD2>BEVERAGES, ALCOHOLIC</FIELD2><FIELD3>123</FIELD3><FIELD4>FEMA</FIELD4><FIELD5>NO</FIELD5></FSDATAMATRIX></DATAMATRIX>' PRINT @XMLSTRING EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLSTRING SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) EXEC sp_xml_removedocument @idoc The problem I'm having is that I can't seem to get the values of FIELD1, FIELD2, FIELD3 by using the OPENXML procedure. Can someone shed some light on this ? Thanks.
Hi, couldn't quite reproduce the problem.
SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX/FIELD1',1)
id parentid nodetype localname prefix namespaceuri datatype prev text
4 2 1 FIELD1 NULL NULL NULL 3 NULL
9 4 3 #text NULL NULL NULL NULL UNITED STATESSince text is independent node inside Field1 this doesn't return a single value but two rows, one for node and one for content Mika
-
Hi, couldn't quite reproduce the problem.
SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX/FIELD1',1)
id parentid nodetype localname prefix namespaceuri datatype prev text
4 2 1 FIELD1 NULL NULL NULL 3 NULL
9 4 3 #text NULL NULL NULL NULL UNITED STATESSince text is independent node inside Field1 this doesn't return a single value but two rows, one for node and one for content Mika
Oops. I should have stated that my code does work, but not the manner I was hoping. Yes, I got the same results you are seeing but I wanted to be able to see 1 row returned. In an ideal world, it would have showed me "FIELD1" and the value of "UNITED STATES". I'm a novice at XML processing, so please bear with me. Is what I'm asking for possible ? If not I will have to work around it by using some logic that will link the two records together by the node type and parent id. Thanks again for the quick response.
-
Oops. I should have stated that my code does work, but not the manner I was hoping. Yes, I got the same results you are seeing but I wanted to be able to see 1 row returned. In an ideal world, it would have showed me "FIELD1" and the value of "UNITED STATES". I'm a novice at XML processing, so please bear with me. Is what I'm asking for possible ? If not I will have to work around it by using some logic that will link the two records together by the node type and parent id. Thanks again for the quick response.
No problem :) One way is to use SQL to manipulate results. For example:
SELECT set1.localname, set2.text
FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) set1
INNER JOIN
OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) set2
ON set1.id = set2.parentid
WHERE set1.LocalName = 'FIELD1'Remember that you can define the columns you need from the xml by using
WITHclause. Of course if you can modify the XML, you could set the texts as attributes instead of elements, which could simplify your code. Hope this helps, Mika -
Below is a snipet of code that I'm using to develop some XML processing. SET @XMLSTRING = '<DATAMATRIX> <FSDATAMATRIX> <DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE> <FIELD1>UNITED STATES</FIELD1><FIELD2>BEVERAGES, ALCOHOLIC</FIELD2><FIELD3>123</FIELD3><FIELD4>FEMA</FIELD4><FIELD5>NO</FIELD5></FSDATAMATRIX></DATAMATRIX>' PRINT @XMLSTRING EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLSTRING SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) EXEC sp_xml_removedocument @idoc The problem I'm having is that I can't seem to get the values of FIELD1, FIELD2, FIELD3 by using the OPENXML procedure. Can someone shed some light on this ? Thanks.
SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) WITH ( DM_OBJECT_TYPE VARCHAR(100) 'DM_OBJECT_TYPE' , FIELD1 VARCHAR(100) 'FIELD1' , FIELD2 VARCHAR(100) 'FIELD2' , FIELD3 VARCHAR(100) 'FIELD3' , FIELD4 VARCHAR(100) 'FIELD4' , FIELD5 VARCHAR(100) 'FIELD5' )Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
-
Below is a snipet of code that I'm using to develop some XML processing. SET @XMLSTRING = '<DATAMATRIX> <FSDATAMATRIX> <DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE> <FIELD1>UNITED STATES</FIELD1><FIELD2>BEVERAGES, ALCOHOLIC</FIELD2><FIELD3>123</FIELD3><FIELD4>FEMA</FIELD4><FIELD5>NO</FIELD5></FSDATAMATRIX></DATAMATRIX>' PRINT @XMLSTRING EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLSTRING SELECT * FROM OPENXML (@idoc, '/DATAMATRIX/FSDATAMATRIX',1) EXEC sp_xml_removedocument @idoc The problem I'm having is that I can't seem to get the values of FIELD1, FIELD2, FIELD3 by using the OPENXML procedure. Can someone shed some light on this ? Thanks.
Thank you to those who have responded, I can get the data XML results. The next problem is that I need to be able to Insert, Update and Delete XML data stored in one of my tables. The table consists of a primary key and a "TEXT_DATA" column that contains the XML data. The wrinkle here is that the TEXT_DATA column is defined as ntext. I'm struggling with XQUERY commands to manipulate the data. Your help is greatly appreciated.
-
Thank you to those who have responded, I can get the data XML results. The next problem is that I need to be able to Insert, Update and Delete XML data stored in one of my tables. The table consists of a primary key and a "TEXT_DATA" column that contains the XML data. The wrinkle here is that the TEXT_DATA column is defined as ntext. I'm struggling with XQUERY commands to manipulate the data. Your help is greatly appreciated.
Hi, Try simply casting the text to xml datatype and use the result for xml dml operations. Simple case to test that casting actually works:
SELECT CAST( CAST( '' as ntext) as xml) XmlTypedColumn
Mika p.s. You could start a new thread for separate questions