locked
Convert ntext to XML and write query to select data in xml RRS feed

  • Question

  • User779033375 posted

    Hi All,

    How can we write a query to Convert ntext to XML and select NODE data from converted XML

    Am able to use  CAST(SXMLDATA as XML) to convert ntext to XML, but unable to fetch data from that xml nodes.

    Please suggest

    Wednesday, March 23, 2016 5:54 AM

Answers

  • User-219423983 posted

    Hi saffy,

    I have written a query, but no no data is selected.

    I have created a demo as below to test your T-SQL code and it could return the expected result and you could have a look.

    declare @uploadxml xml
    set @uploadxml = N'<MAIN>
    						<SUB>
    							<ORDERNUM>ORDERNUM001</ORDERNUM>
    							<ORDERNAME>ORDERNAME001</ORDERNAME>
    							<COMMENTS>Review comments-001</COMMENTS>
    						</SUB>
    						<SUB>
    							<ORDERNUM>ORDERNUM002</ORDERNUM>
    							<ORDERNAME>ORDERNAME002</ORDERNAME>
    							<COMMENTS></COMMENTS>
    						</SUB>
    					</MAIN>'
    ;with cte 
    as
    (
    SELECT 
    	ORDERNAME = Node.Data.value('(ORDERNAME)[1]', 'VARCHAR(MAX)') 
        , 
    	[COMMENTS] = Node.Data.value('(COMMENTS)[1]', 'VARCHAR(MAX)')
    FROM    
    	@uploadxml.nodes('/MAIN//SUB') Node(Data)
    )
    select * from cte where COMMENTS is not null and COMMENTS <> ''
    
    
    --The Result is below
    --ORDERNAME	COMMENTS
    --ORDERNAME001	Review comments-001
    

    About why your code not return the right output, I think you’d better first make sure the “@uploadxml” has the right value from the SQL command. According to below demo you could know that you should append “, ELEMENTS” to the T-SQL to the right xml result for @uploadxml”.

    IF OBJECT_ID('tempdb..#XmlTestTable') IS NOT NULL DROP TABLE #XmlTestTable
    CREATE TABLE #XmlTestTable 
    (
        ID INT PRIMARY KEY IDENTITY(1,1),
        FirstName VARCHAR(20),
        LastName VARCHAR(20)
    )
    INSERT INTO #XmlTestTable (FirstName,LastName) VALUES
    ('John','Doe'),
    ('Jane','Doe'),
    ('Brian','Smith'),
    ('Your','Mom')
    
    
    SELECT * FROM #XmlTestTable FOR XML RAW
    SELECT * FROM #XmlTestTable FOR XML RAW, ELEMENTS
    
    DROP TABLE #XmlTestTable
    

    For more things, I suggest you could read below article carefully to learn the FOR XML RAW mode.

    https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

    Best Regards,

    Weibo Zhang

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 6, 2016 8:22 AM

All replies

  • User-62323503 posted

    Refer below post to read nodes from XML

    http://www.itdeveloperzone.com/2012/01/convert-xml-to-table-in-sql-server-2005.html

    Wednesday, March 23, 2016 6:28 AM
  • User77042963 posted

    Can you modify your table to change the column data type ?

     If you can, you can alter your table's column to nvarchar(max) from ntext and do an UPDATE to itself.

    Alter yourtable  

    ALTER COLUMN theCol  NVARCHAR (max) 

    Update yourtable  Set theCol  =theCol  ;

    Wednesday, March 23, 2016 9:04 PM
  • User779033375 posted

    Hi Limno,

    Thank you for reply.

    We cannot alter table.

    Tuesday, March 29, 2016 9:03 AM
  • User779033375 posted

    Hi Sandeep,

    Thank you for reply, will update back ASAP.

    Tuesday, March 29, 2016 9:08 AM
  • User779033375 posted

    Hi All,

    I have written a query, but no no data is selected.

    @uploadxml :: has data but below SELECT returns no rows.

     

     

    declare @uploadxml xml
     set  @uploadxml=( select top 100 CAST(DATA_COLUMN AS XML)   FROM ABC_TABLE WHERE STATUS ='HOLD'
                for XML RAW)
     
     SELECT ORDERNAME = Node.Data.value('(ORDERNAME)[1]', 'VARCHAR(MAX)')
     
                , [COMMENTS] = Node.Data.value('(COMMENTS)[1]', 'VARCHAR(MAX)')
            FROM    @uploadxml.nodes('/MAIN//SUB') Node(Data)
    



    Please suggest.

    Tuesday, March 29, 2016 10:10 AM
  • User-595703101 posted

    Hi saffy,

    How is your XML data structure?

    Can you share some part of it with replaced data if you want, and the output required?

    So we can try to build a query according to your requirement

    Wednesday, March 30, 2016 2:15 PM
  • User779033375 posted

    Hi Eralper,

    Thank you for reply

    Below is xml format for each row.

    <MAIN>
        <SUB>
            <ORDERNUM>ORDERNUM001</ORDERNUM>
            <ORDERNAME>ORDERNAME</ORDERNAME>
            <COMMENTS>Review comments</COMMENTS>
           </SUB>
    </MAIN>

    From below query STATUS is direct columnname

    and in other column we have above xml text.

    Requirement: From XML need to select all ORDERNAME where COMMENTS is not null  

    declare @uploadxml xml
     set  @uploadxml=( select top 100 CAST(DATA_COLUMN AS XML)   FROM ABC_TABLE WHERE STATUS ='HOLD'
                for XML RAW)
     
     SELECT ORDERNAME = Node.Data.value('(ORDERNAME)[1]', 'VARCHAR(MAX)')
     
                , [COMMENTS] = Node.Data.value('(COMMENTS)[1]', 'VARCHAR(MAX)')
            FROM    @uploadxml.nodes('/MAIN//SUB') Node(Data)
    Friday, April 1, 2016 6:04 AM
  • User779033375 posted

    Please suggest.

    Tuesday, April 5, 2016 4:42 AM
  • User-219423983 posted

    Hi saffy,

    I have written a query, but no no data is selected.

    I have created a demo as below to test your T-SQL code and it could return the expected result and you could have a look.

    declare @uploadxml xml
    set @uploadxml = N'<MAIN>
    						<SUB>
    							<ORDERNUM>ORDERNUM001</ORDERNUM>
    							<ORDERNAME>ORDERNAME001</ORDERNAME>
    							<COMMENTS>Review comments-001</COMMENTS>
    						</SUB>
    						<SUB>
    							<ORDERNUM>ORDERNUM002</ORDERNUM>
    							<ORDERNAME>ORDERNAME002</ORDERNAME>
    							<COMMENTS></COMMENTS>
    						</SUB>
    					</MAIN>'
    ;with cte 
    as
    (
    SELECT 
    	ORDERNAME = Node.Data.value('(ORDERNAME)[1]', 'VARCHAR(MAX)') 
        , 
    	[COMMENTS] = Node.Data.value('(COMMENTS)[1]', 'VARCHAR(MAX)')
    FROM    
    	@uploadxml.nodes('/MAIN//SUB') Node(Data)
    )
    select * from cte where COMMENTS is not null and COMMENTS <> ''
    
    
    --The Result is below
    --ORDERNAME	COMMENTS
    --ORDERNAME001	Review comments-001
    

    About why your code not return the right output, I think you’d better first make sure the “@uploadxml” has the right value from the SQL command. According to below demo you could know that you should append “, ELEMENTS” to the T-SQL to the right xml result for @uploadxml”.

    IF OBJECT_ID('tempdb..#XmlTestTable') IS NOT NULL DROP TABLE #XmlTestTable
    CREATE TABLE #XmlTestTable 
    (
        ID INT PRIMARY KEY IDENTITY(1,1),
        FirstName VARCHAR(20),
        LastName VARCHAR(20)
    )
    INSERT INTO #XmlTestTable (FirstName,LastName) VALUES
    ('John','Doe'),
    ('Jane','Doe'),
    ('Brian','Smith'),
    ('Your','Mom')
    
    
    SELECT * FROM #XmlTestTable FOR XML RAW
    SELECT * FROM #XmlTestTable FOR XML RAW, ELEMENTS
    
    DROP TABLE #XmlTestTable
    

    For more things, I suggest you could read below article carefully to learn the FOR XML RAW mode.

    https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

    Best Regards,

    Weibo Zhang

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 6, 2016 8:22 AM
  • User779033375 posted

    Thank you.

    Thursday, April 7, 2016 8:07 AM