SQL Server Search XML column referencing DLL namespace

laik7k3q 于 2023-03-11 发布在其他

关注(0)|答案(2)|浏览(159)

I've been struggling to get a query to return the value of an XML element. The XML contains a few namespaces. The namespace where the tag of interest exists happens to reference a DLL, which i have locally. I need a query which returns all results where <v:Width> is 15.

<ObsFormElementViewModel_Of_string xmlns:m="http://schemas.datacontract.org/2004/07/System.Windows.Media" xmlns:v="SXA.FormBuilder.Common.ViewModel" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="SXA.FormBuilder.FormElementGenerator.ViewModels">
  <v:Categories>Observation</v:Categories>
  <v:DataType>FormattedFreeText</v:DataType>
  <v:Height>402</v:Height>
  <v:TabSequence>200</v:TabSequence>
  <v:Width>15</v:Width>
  <v:X>18</v:X>
  <v:Y>101</v:Y>
  <v:ZOrder>2147383648</v:ZOrder>
  <ControlVM>
    <ContainerVM i:type="ObsContainerViewModel">
      <Height>NaN</Height>
      <MaxHeight>9999</MaxHeight>
      <MaxWidth>9999</MaxWidth>
      <Requirements>None</Requirements>
      <Width>NaN</Width>
      <Border />
    </ContainerVM>
    <ContentVM i:type="ObsFormattedFreeTextViewModel">
      <Height>370</Height>
      <MaxHeight>2180</MaxHeight>
      <MaxWidth>2180</MaxWidth>
      <Requirements>None</Requirements>
      <Width>1028</Width>
      <BorderStyle />
      <MaximumInputLength>500</MaximumInputLength>
      <TextStyle>
        <FontNameString>Segoe UI</FontNameString>
        <FontSize>10</FontSize>
      </TextStyle>
    </ContentVM>
    <LabelVM i:type="ObsLabelViewModel">
      <Height>23</Height>
      <MaxHeight>9999</MaxHeight>
      <MaxWidth>9999</MaxWidth>
      <MinHeight>23</MinHeight>
      <MinWidth>14</MinWidth>
      <Requirements>None</Requirements>
      <Width>1028</Width>
      <BorderStyle>
        <LineStyle>NoLine</LineStyle>
      </BorderStyle>
      <LabelWidth>75</LabelWidth>
      <OverrideLabelAs />
      <Text>PF_RTF_Box1</Text>
      <TextStyle>
        <FontNameString>Segoe UI</FontNameString>
        <FontSize>10</FontSize>
      </TextStyle>
    </LabelVM>
    <ObservationName>PF_RTF_Box1</ObservationName>
    <ObservationType>FreeText</ObservationType>
  </ControlVM>
</ObsFormElementViewModel_Of_string>

My current query which returns nulls for each row is

WITH XMLNAMESPACES ( 'http://schemas.datacontract.org/2004/07/System.Windows.Media' as m,
'SXA.FormBuilder.Common.ViewModel' as v,
'http://www.w3.org/2001/XMLSchema' as x,
'http://www.w3.org/2001/XMLSchema-instance' as i) 

SELECT e.ControlID,
    Y.value('(v:Width)[1]', 'VARCHAR(20)') as Width
FROM SXAFBControl e
OUTER APPLY e.PropertyBagxml.nodes('ObsFormElementViewModel_Of_string') as X(Y)

sql-server

来源：https://stackoverflow.com/questions/75702872/search-xml-column-referencing-dll-namespace

2条答案

按热度按时间

t9eec4r01#

There are five XML namespaces defined in your document:

xmlns:m="http://schemas.datacontract.org/2004/07/System.Windows.Media"
xmlns:v="SXA.FormBuilder.Common.ViewModel"
xmlns:x="http://www.w3.org/2001/XMLSchema"
xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
xmlns="SXA.FormBuilder.FormElementGenerator.ViewModels"

Take note of that last namespace in particular - it has no prefix defined on it so it is the "default" namespace of the document's root element.

There are only two namespaces required to query the specific value that you're seeking in your question, so the following statement will correctly retrieve that data:

WITH XMLNAMESPACES (
  default 'SXA.FormBuilder.FormElementGenerator.ViewModels',
  'SXA.FormBuilder.Common.ViewModel' as v
)
SELECT
  e.ControlID,
  Y.value('(v:Width)[1]', 'VARCHAR(20)') as Width
FROM SXAFBControl e
OUTER APPLY e.PropertyBagxml.nodes('/ObsFormElementViewModel_Of_string') as X(Y);

One thing to be aware of with XML documents is that the namespace prefixes used ( m , v , x and i ) don't actually matter - they are only unique values to linked the namespace URIs themselves. You can use any set of prefixes in your queries, so long as they map to the correct namespace URIs, so the following statement will also retrieve the same data:

WITH XMLNAMESPACES (
  'SXA.FormBuilder.FormElementGenerator.ViewModels' as foo,
  'SXA.FormBuilder.Common.ViewModel' as bar
)
SELECT
  e.ControlID,
  Y.value('(bar:Width)[1]', 'VARCHAR(20)') as Width
FROM SXAFBControl e
OUTER APPLY e.PropertyBagxml.nodes('/foo:ObsFormElementViewModel_Of_string') as X(Y);

赞(0）回复(0）举报 2023-03-11

46scxncf2#

You should be able to skip the whole namespace "thing" by doing a a wildcard ns query:

SELECT e.ControlID,
    Y.value('*:Width[1]', 'VARCHAR(20)') as Width
FROM SXAFBControl e
OUTER APPLY e.PropertyBagxml.nodes('*:ObsFormElementViewModel_Of_string') as X(Y)

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server Search XML column referencing DLL namespace

2条答案

相关问题

热门标签

最新问答