Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    103

    Unanswered: Parsing XML Data

    SQL Server 2005

    I am new to playing with XML, but I have the following that returns what seems to be a valid recordset. How do I send the results to a table?

    DECLARE @doc_handle int
    DECLARE @xml xml

    select @xml = XMLData
    from dbo.TblXML
    where [ID] = 1

    BEGIN TRY
    --Prepare xml document
    EXEC sp_xml_preparedocument @doc_handle OUTPUT, @xml

    SELECT
    nref.value('Field1[1]', 'nvarchar(50)') Field1,
    nref.value('Field2[1]', 'nvarchar(50)') Field2
    FROM @xml.nodes('//Customer') as R(nref)
    END TRY

    BEGIN CATCH
    --Always unprepare document
    EXEC sp_xml_removedocument @doc_handle
    END CATCH


    I know the following DOES NOT WORK, but I want to know how to do what following intends - and that is send the results to a new table.

    DECLARE @doc_handle int
    DECLARE @xml xml

    select @xml = XMLData
    from dbo.TblXML
    where [ID] = 1

    BEGIN TRY
    --Prepare xml document
    EXEC sp_xml_preparedocument @doc_handle OUTPUT, @xml

    SELECT
    nref.value('Field1[1]', 'nvarchar(50)') Field1,
    nref.value('Field2[1]', 'nvarchar(50)') Field2
    into NewTbl
    FROM @xml.nodes('//Customer') as R(nref)
    END TRY

    BEGIN CATCH
    --Always unprepare document
    EXEC sp_xml_removedocument @doc_handle
    END CATCH


    FYI - the XMLData field is of type XML, but it is untyped XML.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    You are mixing SQL Server 2000 approach wíth SQL Server 2005 appoach.
    Choose one and stick by it. Preferably SQL Server 2005 approach.

    Code:
    DECLARE	@xml XML 
    
    SET	@xml = '<Customers> 
    			<Customer> 
    				<FirstName>Kevin</FirstName> 
    				<LastName>Goff</LastName> 
    				<City type="aca">Camp Hill</City> 
    			</Customer> 
    			<Customer> 
    				<FirstName>Steve</FirstName> 
    				<LastName>Goff</LastName> 
    				<City type="acb"> Philadelphia</City> 
    			</Customer> 
    		</Customers>'
    
    --INSERT	TargetTable
    --	(
    --		fn,
    --		en,
    --		city,
    --		typ
    --	)
    SELECT	t.c.value('FirstName[1]', 'VARCHAR(50)') AS FirstName,
    	t.c.value('LastName[1]', 'VARCHAR(50)') AS LastName,
    	t.c.value('City[1]', 'VARCHAR(50)') AS City,
    	t.c.value('City[1]/@type', 'VARCHAR(50)') AS Type
    FROM	@xml.nodes('Customers/Customer') AS t(c)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    HEY

    Don't you have enough to do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •