query equivalent: document

Showing posts with label document. Show all posts

Wednesday, March 28, 2012

looping through xml document with OpenXML

Hi,
I am trying to compare records from an XML document to records in a table.
If the records in the XML document are not in the table then I wish to
insert those records.
How do I loop through the XML document within a store proc?
Has anyone done this who can provide a sample.
Thanks
Rather than looping, it'd probably be more efficient to use a NOT IN clause.
Have a look at the following example and see if you can adapt it for your
data:
USE Northwind
GO
DECLARE @.productsDoc nvarchar(2000)
SET @.productsDoc = '
<Products>
<Product ProductID="1">
<ProductName>Chai</ProductName>
<UnitPrice>10</UnitPrice>
</Product>
<Product ProductID="101">
<ProductName>Porridge</ProductName>
<UnitPrice>16</UnitPrice>
</Product>
<Product ProductID="102">
<ProductName>Haggis</ProductName>
<UnitPrice>19</UnitPrice>
</Product>
</Products>'
DECLARE @.h integer
EXEC sp_xml_preparedocument @.h OUTPUT, @.productsDoc
SET IDENTITY_INSERT Products ON
INSERT Products (ProductID, ProductName, UnitPrice)
SELECT * FROM
OPENXML(@.h, 'Products/Product', 2)
WITH
(
ProductID integer '@.ProductID',
ProductName nvarchar(40),
UnitPrice money
)
WHERE ProductID NOT IN
(SELECT ProductID FROM Products)
SET IDENTITY_INSERT Products OFF
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:8CF49B69-9E84-4B7D-96AE-C2E0FCE854E2@.microsoft.com...
Hi,
I am trying to compare records from an XML document to records in a table.
If the records in the XML document are not in the table then I wish to
insert those records.
How do I loop through the XML document within a store proc?
Has anyone done this who can provide a sample.
Thanks
|||Thanks Graeme.
Looks like your code should work and I will give it a try.
"Graeme Malcolm" wrote:

> Rather than looping, it'd probably be more efficient to use a NOT IN clause.
> Have a look at the following example and see if you can adapt it for your
> data:
> USE Northwind
> GO
> DECLARE @.productsDoc nvarchar(2000)
> SET @.productsDoc = '
> <Products>
> <Product ProductID="1">
> <ProductName>Chai</ProductName>
> <UnitPrice>10</UnitPrice>
> </Product>
> <Product ProductID="101">
> <ProductName>Porridge</ProductName>
> <UnitPrice>16</UnitPrice>
> </Product>
> <Product ProductID="102">
> <ProductName>Haggis</ProductName>
> <UnitPrice>19</UnitPrice>
> </Product>
> </Products>'
> DECLARE @.h integer
> EXEC sp_xml_preparedocument @.h OUTPUT, @.productsDoc
> SET IDENTITY_INSERT Products ON
> INSERT Products (ProductID, ProductName, UnitPrice)
> SELECT * FROM
> OPENXML(@.h, 'Products/Product', 2)
> WITH
> (
> ProductID integer '@.ProductID',
> ProductName nvarchar(40),
> UnitPrice money
> )
> WHERE ProductID NOT IN
> (SELECT ProductID FROM Products)
> SET IDENTITY_INSERT Products OFF
> Hope that helps,
> Graeme
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Opa" <Opa@.discussions.microsoft.com> wrote in message
> news:8CF49B69-9E84-4B7D-96AE-C2E0FCE854E2@.microsoft.com...
> Hi,
> I am trying to compare records from an XML document to records in a table.
> If the records in the XML document are not in the table then I wish to
> insert those records.
> How do I loop through the XML document within a store proc?
> Has anyone done this who can provide a sample.
> Thanks
>
>

Monday, March 12, 2012

Lookup on a range

I want to check to see if my document number (let's say, 11) is in between two values: from a table in my database, I have a min value and a max value; how can I check to see if the number is between the min and the max?

Thanks!

Jim Work
In the control flow, you may want to issue an execute sql task with the query being: select min(value) as minvalue, max(value) as maxvalue from table.

Then map the results to two variables.

Then, in the data flow, you can compare input to the two variables using a conditional split, derived column, etc...

Saturday, February 25, 2012

looking for nested XML document as output on a recursive query from sql table

Is it possible to finally have a nested XML document on a recursive query? Let's say that we have a classic parent-child relationship inside a table that is self-refernced. An example of this situation can be found in the Employees table of Northwind DB or in the shining new AdventureWorks DB, HumanResources.Employee table.
In both cases we have somthing like that:

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)
AS
(
SELECT
ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
FROM
HumanResources.Employee
WHERE
ManagerID IS NULL

UNION ALL

SELECT
e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))
FROM
HumanResources.Employee AS e
JOIN
DirectReports AS d
ON
e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports
ORDER BY Path

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

I am really just starting to learn XML and so I am not sure that I understand your question correctly. Here is some code that seems to produce the output you have requested. Would you please give this a look and tell me if this is headed in the right direction?

Code Snippet

declare @.employee table
( EmployeeId integer,
ManagerId integer,
DepartmentId integer,
Title varchar(18)
)
insert into @.employee
select 109, null, 1, 'Top Dog' union all
select 12, 109, 1, 'Second Dog' union all
select 3, 12, 1, '2-Dog''s Dog' union all
select 4, 3, 1, 'Just Another Dog' union all
select 9, 3, 1, 'Just Another Dog' union all
select 11, 3, 1, 'Just Another Dog' union all
select 158, 3, 1, 'Just Another Dog' union all
select 79, 158, 1, 'Just Another Dog' union all
select 114, 158,1, 'Just Another Dog' union all
select 217, 158,1, 'Just Another Dog' union all
select 263, 3, 1, 'Just Another Dog' union all
select 5, 263, 1, 'Just Another Dog' union all
select 265,263, 1, 'Just Another Dog' union all
select 267, 3, 1, 'Just Another Dog' union all
select 270, 3, 1, 'Just Another Dog'
--select * from @.employee

;with hierarchyCTE as
( select 1 as level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
cast(right(' ' + convert(varchar(11), employeeId), 11) as varchar(240))
as path
from @.employee
where ManagerId is null
union all
select a.level + 1,
b.EmployeeId,
a.EmployeeId,
b.DepartmentId,
b.Title,
cast( a.Path + '/' +
right(' ' + convert(varchar(11), b.employeeId), 11)
as varchar(240))
from hierarchyCTE a
join @.employee b
on a.EmployeeId = b.ManagerId
), sequenceCTE as
( select row_number() over (order by path) as Seq,
level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from hierarchyCTE
), maxSeqCTE as
( select max(seq) as maxSeq
from sequenceCTE
), nextLevelCTE as
( select -1 + row_number() over (order by path) as Seq,
Level
from hierarchyCTE
union all
select seq,
1
from sequenceCTE
join maxSeqCTE
on seq = maxSeq
), outputCTE as
( select cast(a.Seq as integer) as Seq1,
1 as Seq2,
a.level,
b.level as nextLevel,
a.EmployeeId,
a.ManagerId,
a.DepartmentId,
a.Title,
a.Path
from sequenceCTE a
join nextLevelCTE b
on a.Seq = b.Seq
union all
select Seq1,
Seq2 + 1,
level - 1,
nextLevel,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from outputCte
where level > nextLevel
)
select replicate (' ', Level - 1) +
case when Seq2 = 1
then '<Emp ' + 'ManagerID="' +
case when ManagerId is null then ''
else convert(varchar(11), ManagerId)
end + '" EmployeeId="' +
convert(varchar(11), EmployeeId) +
'" DepartmentId="' +
convert(varchar(11), Departmentid) +
'" Title="' +
case when Title is null then ''
else Title
end + '"' +
case when level >= nextLevel
then '/' else ''
end + '>'
else '</Emp>'
end as xmlOutput
from outputCTE
order by Seq1, Seq2

/*
xmlOutput
--
<Emp ManagerID="" EmployeeId="109" DepartmentId="1" Title="Top Dog">
<Emp ManagerID="109" EmployeeId="12" DepartmentId="1" Title="Second Dog">
<Emp ManagerID="12" EmployeeId="3" DepartmentId="1" Title="2-Dog's Dog">
<Emp ManagerID="3" EmployeeId="4" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="9" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="11" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="158" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="158" EmployeeId="79" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="114" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="217" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="263" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="263" EmployeeId="5" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="263" EmployeeId="265" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="267" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="270" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
</Emp>
</Emp>
*/

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

looking for nested XML document as output on a recursive query from sql table

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

UNION ALL

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

Code Snippet

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

looking for nested XML document as output on a recursive query from sql table

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

UNION ALL

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

Code Snippet

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

Wednesday, March 28, 2012

looping through xml document with OpenXML

Monday, March 12, 2012

Lookup on a range

Saturday, February 25, 2012

looking for nested XML document as output on a recursive query from sql table

looking for nested XML document as output on a recursive query from sql table

looking for nested XML document as output on a recursive query from sql table

query equivalent

Blog Archive

About Me