3

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.

6

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)
| improve this answer | |
  • Thanks Jaco! I had to put square brackets around [text] due to reserved word issues, but I got my desired results. The help is much appreciated! – Kevin3NF Dec 1 '15 at 20:30
  • I added the square brackets in my answer, thanks for pointing it out. – Alex Dec 1 '15 at 20:33
3

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);
| improve this answer | |
  • Thanks for the extra option Mikael. I will check both of these out today side by side. As you can imagine, there is quite a bit more going on here. The XML I provided was just a snippet of a much larger document...in reality, there could be one to thousands of HostProperty segments...and I have no control of the incoming data :) – Kevin3NF Dec 2 '15 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.