OPENXML question for SQL Server

Question

I am brand new to OPENXML, and have 90 % of what I want to do working...stuck on this:

<ReportHost name="411.114.42.42">
  <HostProperties>
    <tag name="HOST_END">Wed Nov 11 20:48:43 2015</tag>
    <tag name="LastAuthenticatedResults">8675309</tag>
    <tag name="Credentialed_Scan">true</tag>
    <tag name="policy-used">AWT v6</tag>
    <tag name="ssh-auth-meth">MyPassword</tag>
    <tag name="ssh-login-used">MyLogin</tag>
    <tag name="local-checks-proto">ssh</tag>
    <tag name="host-ip">411.114.42.42</tag>
    <tag name="HOST_START">Wed Nov 11 20:00:01 2015</tag>
  </HostProperties>
</ReportHost>

My Desired results:

Wed Nov 11 20:48:43 2015
8675309
TRUE
AWT v6
MyPassword
MyLogin
ssh
411.114.42.42
Wed Nov 11 20:00:01 2015

I am able to get the name value ("Host_end", etc.), but not the content.

My query:

Select   * 
from OPENXML (@hdoc,'/ReportHost/HostProperties/tag',1)
WITH (
    name varchar (500)
     )

I'd like to stick with OPENXML if at all possible, since I just spent the day learning it and getting to this final step.

Please let me know if I have provided enough info.

Answer 1

You need to query for nodetype 3 to get the element text. I have changed your xpath expression slightly so it just returns the node text

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XmlDocument
Select   [text]
from OPENXML (@hdoc,'/ReportHost/HostProperties/tag/text()',3)

Answer 2

OPENXML (Transact-SQL)

Extend the SchemaDeclaration with a ColPattern to specify a different XPath than what you get from the default behaviour of ColName.

declare @X xml = '
<ReportHost name="411.114.42.42">
  <HostProperties>
    <tag name="HOST_END">Wed Nov <B>11</B> 20:48:43 2015</tag>
    <tag name="LastAuthenticatedResults">8675309</tag>
    <tag name="Credentialed_Scan">true</tag>
    <tag name="policy-used">AWT v6</tag>
    <tag name="ssh-auth-meth">MyPassword</tag>
    <tag name="ssh-login-used">MyLogin</tag>
    <tag name="local-checks-proto">ssh</tag>
    <tag name="host-ip">411.114.42.42</tag>
    <tag name="HOST_START">Wed Nov 11 20:00:01 2015</tag>
  </HostProperties>
</ReportHost>';

declare @hdoc int;

exec sp_xml_preparedocument @hdoc out, @X;

select T.Name,
       T.Value
from openxml(@hdoc, '/ReportHost/HostProperties/tag')
  with (
       Name varchar(500) '@name',
       Value varchar(500) '.'
       ) as T;

exec sp_xml_removedocument @hdoc;

You are shredding on the tag node so . specifies the current node value and and if you use @ to specify that you want an attribute you don't have to care about the flags parameter to OPENXML.

I'd like to stick with OPENXML if at all possible, since I just spent the day learning it and getting to this final step.

I would recommend you to spend some time with the XML data type instead. The same query in that case would look like this.

declare @X xml = '
<ReportHost name="411.114.42.42">
  <HostProperties>
    <tag name="HOST_END">Wed Nov <B>11</B> 20:48:43 2015</tag>
    <tag name="LastAuthenticatedResults">8675309</tag>
    <tag name="Credentialed_Scan">true</tag>
    <tag name="policy-used">AWT v6</tag>
    <tag name="ssh-auth-meth">MyPassword</tag>
    <tag name="ssh-login-used">MyLogin</tag>
    <tag name="local-checks-proto">ssh</tag>
    <tag name="host-ip">411.114.42.42</tag>
    <tag name="HOST_START">Wed Nov 11 20:00:01 2015</tag>
  </HostProperties>
</ReportHost>';

select T.X.value('@name', 'varchar(500)') as Name,
       T.X.value('.', 'varchar(500)') as Value
from @X.nodes('/ReportHost/HostProperties/tag') as T(X);