 |
|
|
|
Next: Data Warehouse Architect- SQL Server 2005, Modeli..
|
| Author |
Message |
External
Since: May 17, 2008
Posts: 10
|
(Msg. 1) Posted: Wed Jul 30, 2008 12:11 pm
Post subject: Help with SELECT FROM OpenXML
Archived from groups: microsoft>public>sqlserver>xml (more info?)
|
|
|
In the FROM below what is the correct syntax for the following?
IsNew="-1" AND IsDeleted="0"
Declare @DetailsXML XML
SET @DetailsXML = '
<OrderDetails>
<OrderDetail LineNumber = "1" ProductID = "99" IsNew = "0" IsDirty =
"-1" IsDeleted = "0"/>
<OrderDetail LineNumber = "2" ProductID = "1" IsNew = "0" IsDirty =
"0" IsDeleted = "-1"/>
<OrderDetail LineNumber = "5" ProductID = "7" IsNew = "-1" IsDirty =
"0" IsDeleted = "0"/>
</OrderDetails>'
Declare @idoc int
Exec sp_xml_preparedocument @idoc OUTPUT, @DetailsXML
SELECT LineNumber, ProductID
FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1"]', 1)
WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
INT)
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774 |
|
| Back to top |
|
 | |
External
Since: Feb 08, 2005
Posts: 94
|
(Msg. 2) Posted: Thu Jul 31, 2008 3:55 am
Post subject: RE: Help with SELECT FROM OpenXML [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
SELECT LineNumber, ProductID
FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1" and
@IsDeleted="0"]', 1)
WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
INT)
work ok for you?
HTH
wBob
Rate the post
"Patrick Jackman" wrote:
> In the FROM below what is the correct syntax for the following?
> IsNew="-1" AND IsDeleted="0"
>
>
> Declare @DetailsXML XML
>
> SET @DetailsXML = '
> <OrderDetails>
> <OrderDetail LineNumber = "1" ProductID = "99" IsNew = "0" IsDirty =
> "-1" IsDeleted = "0"/>
> <OrderDetail LineNumber = "2" ProductID = "1" IsNew = "0" IsDirty =
> "0" IsDeleted = "-1"/>
> <OrderDetail LineNumber = "5" ProductID = "7" IsNew = "-1" IsDirty =
> "0" IsDeleted = "0"/>
> </OrderDetails>'
>
> Declare @idoc int
> Exec sp_xml_preparedocument @idoc OUTPUT, @DetailsXML
>
> SELECT LineNumber, ProductID
> FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1"]', 1)
> WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
> INT)
>
> Patrick
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Patrick Jackman
> Vancouver, BC
> 604-874-5774
>
>
> |
|
| Back to top |
|
| |
External
Since: May 17, 2008
Posts: 10
|
(Msg. 3) Posted: Thu Jul 31, 2008 9:00 am
Post subject: Re: Help with SELECT FROM OpenXML [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks Bob.
I discovered that this will also work:
SELECT LineNumber, ProductID
FROM OpenXML(@idoc, 'OrderDetails/OrderDetail', 1)
WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
INT)
WHERE IsNew='-1' AND IsDeleted = '0'
Would there be any performance reasons to use one over the other?
Patrick.
"Bob" wrote in message
SELECT LineNumber, ProductID
FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1" and
@IsDeleted="0"]', 1)
WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
INT)
work ok for you?
HTH
wBob
Rate the post
"Patrick Jackman" wrote:
> In the FROM below what is the correct syntax for the following?
> IsNew="-1" AND IsDeleted="0"
>
>
> Declare @DetailsXML XML
>
> SET @DetailsXML = '
> <OrderDetails>
> <OrderDetail LineNumber = "1" ProductID = "99" IsNew = "0" IsDirty
> =
> "-1" IsDeleted = "0"/>
> <OrderDetail LineNumber = "2" ProductID = "1" IsNew = "0" IsDirty
> =
> "0" IsDeleted = "-1"/>
> <OrderDetail LineNumber = "5" ProductID = "7" IsNew = "-1" IsDirty
> =
> "0" IsDeleted = "0"/>
> </OrderDetails>'
>
> Declare @idoc int
> Exec sp_xml_preparedocument @idoc OUTPUT, @DetailsXML
>
> SELECT LineNumber, ProductID
> FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1"]', 1)
> WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
> INT)
>
> Patrick
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Patrick Jackman
> Vancouver, BC
> 604-874-5774
>
>
> >> Stay informed about: Help with SELECT FROM OpenXML |
|
| Back to top |
|
| |
External
Since: Feb 08, 2005
Posts: 94
|
(Msg. 4) Posted: Thu Jul 31, 2008 10:35 am
Post subject: Re: Help with SELECT FROM OpenXML [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
I'm not aware of any. Trial it and see.
"Patrick Jackman" wrote:
> Thanks Bob.
>
> I discovered that this will also work:
> SELECT LineNumber, ProductID
> FROM OpenXML(@idoc, 'OrderDetails/OrderDetail', 1)
> WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
> INT)
> WHERE IsNew='-1' AND IsDeleted = '0'
>
> Would there be any performance reasons to use one over the other?
>
> Patrick.
>
> "Bob" wrote in message
>
> SELECT LineNumber, ProductID
> FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1" and
> @IsDeleted="0"]', 1)
> WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
> INT)
>
> work ok for you?
> HTH
> wBob
> Rate the post
>
> "Patrick Jackman" wrote:
>
> > In the FROM below what is the correct syntax for the following?
> > IsNew="-1" AND IsDeleted="0"
> >
> >
> > Declare @DetailsXML XML
> >
> > SET @DetailsXML = '
> > <OrderDetails>
> > <OrderDetail LineNumber = "1" ProductID = "99" IsNew = "0" IsDirty
> > =
> > "-1" IsDeleted = "0"/>
> > <OrderDetail LineNumber = "2" ProductID = "1" IsNew = "0" IsDirty
> > =
> > "0" IsDeleted = "-1"/>
> > <OrderDetail LineNumber = "5" ProductID = "7" IsNew = "-1" IsDirty
> > =
> > "0" IsDeleted = "0"/>
> > </OrderDetails>'
> >
> > Declare @idoc int
> > Exec sp_xml_preparedocument @idoc OUTPUT, @DetailsXML
> >
> > SELECT LineNumber, ProductID
> > FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1"]', 1)
> > WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
> > INT)
> >
> > Patrick
> > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> > Patrick Jackman
> > Vancouver, BC
> > 604-874-5774
> >
> >
> >
>
>
> >> Stay informed about: Help with SELECT FROM OpenXML |
|
| Back to top |
|
| |
External
Since: Aug 18, 2008
Posts: 2
|
(Msg. 5) Posted: Mon Aug 18, 2008 8:48 am
Post subject: Re: Help with SELECT FROM OpenXML [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
My experience is that, applying a filter within the XPath expression gives
better performance.
Like Bob said, you should do some testing on your side to confirm this.
--
Jacob Sebastian
SQL Server MVP
http://www.sqlserverandxml.com
"Patrick Jackman" wrote in message
> Thanks Bob.
>
> I discovered that this will also work:
> SELECT LineNumber, ProductID
> FROM OpenXML(@idoc, 'OrderDetails/OrderDetail', 1)
> WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
> INT)
> WHERE IsNew='-1' AND IsDeleted = '0'
>
> Would there be any performance reasons to use one over the other?
>
> Patrick.
>
> "Bob" wrote in message
>
> SELECT LineNumber, ProductID
> FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1" and
> @IsDeleted="0"]', 1)
> WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT, IsDeleted
> INT)
>
> work ok for you?
> HTH
> wBob
> Rate the post
>
> "Patrick Jackman" wrote:
>
>> In the FROM below what is the correct syntax for the following?
>> IsNew="-1" AND IsDeleted="0"
>>
>>
>> Declare @DetailsXML XML
>>
>> SET @DetailsXML = '
>> <OrderDetails>
>> <OrderDetail LineNumber = "1" ProductID = "99" IsNew = "0"
>> IsDirty
>> =
>> "-1" IsDeleted = "0"/>
>> <OrderDetail LineNumber = "2" ProductID = "1" IsNew = "0" IsDirty
>> =
>> "0" IsDeleted = "-1"/>
>> <OrderDetail LineNumber = "5" ProductID = "7" IsNew = "-1"
>> IsDirty
>> =
>> "0" IsDeleted = "0"/>
>> </OrderDetails>'
>>
>> Declare @idoc int
>> Exec sp_xml_preparedocument @idoc OUTPUT, @DetailsXML
>>
>> SELECT LineNumber, ProductID
>> FROM OpenXML(@idoc, 'OrderDetails/OrderDetail[@IsNew="-1"]', 1)
>> WITH (LineNumber INT, ProductID INT, IsNew INT, IsDirty INT,
>> IsDeleted
>> INT)
>>
>> Patrick
>> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
>> Patrick Jackman
>> Vancouver, BC
>> 604-874-5774
>>
>>
>>
>
> >> Stay informed about: Help with SELECT FROM OpenXML |
|
| Back to top |
|
| |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
