Post reply

Need to select all elements of an XML document from an XML data type column

  • December 3, 2010 at 8:26 am

    #1258436

    Hey! Not sure if you still care, or if this is what you are looking for, but here it is anyway 🙂

    This assumes you know the root element name. You can store output in temp tables or table variables and process it any way you want.

    DECLARE @idoc INT

    DECLARE @exml XML

    DECLARE @Root VARCHAR(64)

    SET @Root = '/YourRootName'

    SELECT @exml = [YourXMLColumn] FROM YourTable

    EXEC sp_xml_preparedocument @idoc OUTPUT, @exml

    select

    columnNames.id,

    columnNames.localname AS ELEMENT,

    columnValue.text AS VALUE

    FROM OPENXML (@idoc, @Root) columnNames

    cross apply (select text FROM OPENXML (@idoc, @Root)

    where nodetype = 3 AND parentid = columnNames.id) columnValue

    where columnNames.nodetype <> 3 and columnNames.localname not in ('xsi','xsd')

    Exec sp_xml_removedocument @idoc

  • December 3, 2010 at 9:54 am

    #1258529

    Thanks for the reply. I used a another nasty SQL script to get the data I needed parsed from the XML docs that were stored in the table into another table so they could report on it. I appreciate your response.

    I've attached the "ParseEPOSFlightDetail.txt" file for your review.

    -David 😀

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply