XML xs:选择 SQLXMLBulkload

发布于 2024-07-17 18:51:45 字数 965 浏览 6 评论 0原文

我有一些 XML 文件需要批处理到 SQL Server 中。以下架构和 XML 部分概述了我遇到问题的一个领域。

<xs:complexType>
    <xs:sequence>
        <xs:choice maxOccurs="unbounded">
            <xs:element name="TextLine" type="xs:string" sql:field="AdvertLine" sql:relation="XmlAdvert" sql:relationship="XmlAdvert" />
            <xs:element name="BreakPoint" sql:is-constant="1" />
        </xs:choice>
    </xs:sequence>
</xs:complexType>

<Advert>
 <AdvertText>
  <TextLine>Isuzu 4 X 4TRUCKMAN</TextLine> 
  <BreakPoint /> 
  <TextLine>2.0TD, Red, 5 dr, 60,000 miles, MOT, 5 SEATER</TextLine> 
  <BreakPoint /> 
  <TextLine>£2500</TextLine>
  <BreakPoint /> 
  <TextLine>01234 567890</TextLine> 
 </AdvertText>
</Advert>

但由于 SQLXMLBulkload 不支持 xs:choice，我想知道是否有另一种方式来表示这一点，因为没有 xs:choice 部分，xs:sequence 一旦到达第二个 TextLine 就无效。

原文

I have some XML files that I need to batch process into SQL Server. The following Schema and XML sections outline an area I'm having trouble with.

<xs:complexType>
    <xs:sequence>
        <xs:choice maxOccurs="unbounded">
            <xs:element name="TextLine" type="xs:string" sql:field="AdvertLine" sql:relation="XmlAdvert" sql:relationship="XmlAdvert" />
            <xs:element name="BreakPoint" sql:is-constant="1" />
        </xs:choice>
    </xs:sequence>
</xs:complexType>

<Advert>
 <AdvertText>
  <TextLine>Isuzu 4 X 4TRUCKMAN</TextLine> 
  <BreakPoint /> 
  <TextLine>2.0TD, Red, 5 dr, 60,000 miles, MOT, 5 SEATER</TextLine> 
  <BreakPoint /> 
  <TextLine>£2500</TextLine>
  <BreakPoint /> 
  <TextLine>01234 567890</TextLine> 
 </AdvertText>
</Advert>

But since xs:choice isn't supported in SQLXMLBulkload I wondered if there was another way of representing this as without the xs:choice section the xs:sequence is invalid as soon as it hits the second TextLine.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

靑春怀旧 2024-07-24 18:51:45

我不熟悉 SQLXMLBulkload，但这是我的想法：

我不确定相邻的 TextLine 和 BreakPoint 元素之间是否存在任何关系。假设不存在，那么最简单的解决方案可能是使用 XSLT 来转换架构和数据，以消除对选择的需要。

请注意，我已在两个元素上插入 maxOccurs="unbounded"。如果这不起作用，还有一种替代方法应该可行；见下文。

<xs:complexType>
    <xs:sequence>
        <xs:element name="TextLine" type="xs:string" sql:field="AdvertLine" sql:relation="XmlAdvert" sql:relationship="XmlAdvert" maxOccurs="unbounded"/>
        <xs:element name="BreakPoint" sql:is-constant="1" maxOccurs="unbounded"/>
    </xs:sequence>
</xs:complexType>

等效的 XML 是：

<Advert>
 <AdvertText>
  <TextLine>Isuzu 4 X 4TRUCKMAN</TextLine> 
  <TextLine>2.0TD, Red, 5 dr, 60,000 miles, MOT, 5 SEATER</TextLine> 
  <TextLine>£2500</TextLine>
  <TextLine>01234 567890</TextLine> 
  <BreakPoint /> 
  <BreakPoint /> 
  <BreakPoint /> 
 </AdvertText>
</Advert>

但据我所知，SQLXMLBulkload 也不喜欢这种安排。在这种情况下，仅基于您的示例 XML，我敢打赌它会接受以下内容：

匹配的 XML 为：

<Advert>
 <AdvertText>
    <TextLines>
      <TextLine>Isuzu 4 X 4TRUCKMAN</TextLine> 
      <TextLine>2.0TD, Red, 5 dr, 60,000 miles, MOT, 5 SEATER</TextLine> 
      <TextLine>£2500</TextLine>
      <TextLine>01234 567890</TextLine>
    </TextLines> 
  <BreakPoints> 
      <BreakPoint /> 
      <BreakPoint /> 
      <BreakPoint /> 
  </BreakPoints> 
 </AdvertText>
</Advert>

这仍然留下了如何编写您需要的 XSLT 的问题，但也许这是一个开始。

I'm not familiar with SQLXMLBulkload, but here are my thoughts:

I'm not sure if there's any relationship between adjacent TextLine and BreakPoint elements. Assuming that there isn't, then the simplest solution may be to use XSLT to transform the schema and data to eliminate the need for the choice.

Note that I've inserted maxOccurs="unbounded" on both elements. If that doesn't work, there's an alternate approach that should work; see below.

<xs:complexType>
    <xs:sequence>
        <xs:element name="TextLine" type="xs:string" sql:field="AdvertLine" sql:relation="XmlAdvert" sql:relationship="XmlAdvert" maxOccurs="unbounded"/>
        <xs:element name="BreakPoint" sql:is-constant="1" maxOccurs="unbounded"/>
    </xs:sequence>
</xs:complexType>

The equivalent XML would be:

<Advert>
 <AdvertText>
  <TextLine>Isuzu 4 X 4TRUCKMAN</TextLine> 
  <TextLine>2.0TD, Red, 5 dr, 60,000 miles, MOT, 5 SEATER</TextLine> 
  <TextLine>£2500</TextLine>
  <TextLine>01234 567890</TextLine> 
  <BreakPoint /> 
  <BreakPoint /> 
  <BreakPoint /> 
 </AdvertText>
</Advert>

But for all I know, SQLXMLBulkload won't like that arrangement either. In that case, based only on your example XML, I would bet that it would accept this:

with the matching XML being:

<Advert>
 <AdvertText>
    <TextLines>
      <TextLine>Isuzu 4 X 4TRUCKMAN</TextLine> 
      <TextLine>2.0TD, Red, 5 dr, 60,000 miles, MOT, 5 SEATER</TextLine> 
      <TextLine>£2500</TextLine>
      <TextLine>01234 567890</TextLine>
    </TextLines> 
  <BreakPoints> 
      <BreakPoint /> 
      <BreakPoint /> 
      <BreakPoint /> 
  </BreakPoints> 
 </AdvertText>
</Advert>

That still leaves open the question of how to write the XSLT that you'd need, but perhaps it's a start.

回复收藏 0 原文

~没有更多了~