Post reply

Trying to read XML

  • October 6, 2005 at 8:18 am

    #89464

    I am wanting to pull in an XML document, and be able to display it like a table.  It looks like the following code should work, but it isn't. What am I doing wrong? 

    DECLARE @RetVal int, @Doc varchar(8000), @iDoc int

    SELECT @Doc = '

    <?xml version="1.0" encoding="UTF-8" standalone="no" ?>

    <policy description="Property file"

    name="Desktop Properties" schema-version="1.0.0" valid="false" version="1.0.0"

    xmlns="http://www.msn.com/cml/Desktop/property">

    <DesktopProperties   Account="bill"

    ClockDifference="-6134"  Controller="Manager_DUMBLEDORE@192.168.1.125"

    HostIP="192.168.1.224"   Installer="UNKNOWN"

    LastConfigUpdateTimestamp=""  LastHeartbeatTimestamp="2005-03-26T18:55:56"

    PolicyGroup="Ing_7ebo_to8"  Type="Windows Desktop"

    Version="7.0.14">

    <AgentParameters Name="pamver" Value="7.0.14"/>

    <AgentParameters Name="tree.group" Value="Ing_7ebo_to8"/>

    <AgentParameters Name="path" Value="C:\Program Files\ms\windows\something.exe"/>

    <AgentParameters Name="agtype" Value="1"/>

    <AgentParameters Name="user" Value="Administrator"/>

    <AgentParameters Name="os" Value="Win2K"/>

    <AgentParameters Name="profile" Value="reporting"/>

    </DesktopProperties>

    </policy>

    '

    EXEC @RetVal = sp_xml_preparedocument @idoc OUTPUT, @doc

    --get data from xml document

    SELECT *

    FROM OPENXML (@idoc, N'/policy/DesktopProperties',1)

     WITH (Account varchar(50),ClockDifference int, Controller varchar(100),

      HostIP varchar(20), Installer varchar(40), LastConfigUpdateTimestamp varchar(40),

      LastHeartbeatTimestamp varchar(40), PolicyGroup varchar(50),

      Type varchar(40), Version Varchar(40))

     

     

  • October 6, 2005 at 9:12 am

    #595513

    There's something funk about your xml string.

    When I remove the bolded section it works fine. (the xmlns Attribute)

    Oh by the way, don't forget the   exec sp_xml_removedocument @idoc

    DECLARE @RetVal int, @Doc varchar(8000), @iDoc int

    SELECT @Doc = '

    <?xml version="1.0" encoding="UTF-8" standalone="no" ?>

    <policy description="Property file"

    name="Desktop Properties" schema-version="1.0.0" valid="false" version="1.0.0"

    xmlns="http://www.msn.com/cml/Desktop/property">

    <DesktopProperties   Account="bill"

    ClockDifference="-6134"  Controller="Manager_DUMBLEDORE@192.168.1.125"

    HostIP="192.168.1.224"   Installer="UNKNOWN"

    LastConfigUpdateTimestamp=""  LastHeartbeatTimestamp="2005-03-26T18:55:56"

    PolicyGroup="Ing_7ebo_to8"  Type="Windows Desktop"

    Version="7.0.14">

    <AgentParameters Name="pamver" Value="7.0.14"/>

    <AgentParameters Name="tree.group" Value="Ing_7ebo_to8"/>

    <AgentParameters Name="path" Value="C:\Program Files\ms\windows\something.exe"/>

    <AgentParameters Name="agtype" Value="1"/>

    <AgentParameters Name="user" Value="Administrator"/>

    <AgentParameters Name="os" Value="Win2K"/>

    <AgentParameters Name="profile" Value="reporting"/>

    </DesktopProperties>

    </policy>

    '

    EXEC @RetVal = sp_xml_preparedocument @idoc OUTPUT, @doc

    --get data from xml document

    SELECT *

    FROM OPENXML (@idoc, N'/policy/DesktopProperties',1)

     WITH (Account varchar(50),ClockDifference int, Controller varchar(100),

      HostIP varchar(20), Installer varchar(40), LastConfigUpdateTimestamp varchar(40),

      LastHeartbeatTimestamp varchar(40), PolicyGroup varchar(50),

      Type varchar(40), Version Varchar(40))

  • October 6, 2005 at 9:23 am

    #595523

    Thanks a ton Ray. I don't work with XML much, so I didn't know what might be tripping it up.  Since I don't need that piece of information, I can parse it out before I send it to the sp.  Thanks again!  

  • October 6, 2005 at 9:43 am

    #595533

    Another question, I also want to access the data in the AgentParameters level.  I am trying this, but getting multiple rows of null values.  Is there a way to get to that information?  

     

    SELECT [tree.group], [User], os

    FROM OPENXML (@idoc, N'/policy/DesktopProperties/AgentParameters',1)

     WITH (pamversion varchar(20), [tree.group] varchar(20), path varchar(100),

      agtype varchar(10), [User] varchar(40), os varchar(40), profile varchar(40))

     

  • October 6, 2005 at 12:24 pm

    #595612

    You have it mixed up.

    SELECT [Name], value

    FROM OPENXML (@idoc, N'/policy/DesktopProperties/AgentParameters',1)

     WITH ([Name] varchar(20),

           [Value] varchar(50))

    <AgentParameters Name="pamver" Value="7.0.14"/>

    your xml consists of an element of AgentParameters,

    with attributes Name, and Value.

    query up top will work.

     

  • October 6, 2005 at 12:30 pm

    #595615

    Awesome!          

  • October 6, 2005 at 2:11 pm

    #595657

    hth

Viewing 7 posts - 1 through 6 (of 6 total)

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