How to handle white space in XML headers?

Admin 1
Viewed 39

SQL Server 2012. Given the following XML:

<header>
    <subheader>
        <Problems>
            <RAW VALUE="1" Sublot="abc"/>
            <RAW VALUE="2" Sublot="def"/>
            <RAW VALUE="3" Sublot="ghi"/>
        </Problems>
    </subheader>
</header>

how does one parse the XML in SQL Server? I an having problems dealing with "RAW VALUE" as a name, such that

SELECT *
FROM OPENXML (@docHandle, '/header/subheader/Problems', 1)
WITH (
    'RAW VALUE' VARCHAR (100)
);

is invalid because of the quote around 'RAW VALUE'. Brackets don't work either. Is there a way to support the space in "RAW VALUE"?

xml sql-server
1 Answers

The attributes of XML elements are accessed with the @ XPath expression prefix, e.g.: @VALUE for the VALUE attribute, @Sublot for the Sublot attribute...

declare @example xml = N'<header>
  <subheader>
    <Problems>
      <RAW VALUE="1" Sublot="abc"/>
      <RAW VALUE="2" Sublot="def"/>
      <RAW VALUE="3" Sublot="ghi"/>
    </Problems>
  </subheader>
</header>';

select
  prob.raw.value('@VALUE', N'nvarchar(100)') as [Value],
  prob.raw.value('@Sublot', N'nvarchar(100)') as [Sublot]
from @example.nodes('/header/subheader/Problems/RAW') prob(raw);

Which yields the result:

Value Sublot
1 abc
2 def
3 ghi
user_3901220
Related