Trying to read XML
-
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))
-
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))
-
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!
-
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))
-
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.
-
Awesome!
-
hth
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply