how to use "WITH" in OPENXML?

Question

SET NOCOUNT ON DECLARE @Products TABLE (product_id VarChar(50),product_name VarChar(50), supplier_one VarChar(50), supplier_two VarChar(50), supplier_three VarChar(50))
INSERT INTO @Products Values ('1','Pen','1001-P1','1002-P1','1003-P1');
INSERT INTO @Products Values ('2','Pencil','1001-P2','1002-P2','1003-P2');
INSERT INTO @Products Values ('3','School Bag','1001-P3','1002-P3','1003-P3');
INSERT INTO @Products Values ('4','Book','1001-P4','1002-P4','1003-P4');
INSERT INTO @Products Values ('5','Pencil Box','1001-P5','1002-P5','1003-P5');

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
<products>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P1</product_id>
    <price>10</price>
    <available_count>10</available_count>
    <remarks></remarks>
</product>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P1</product_id>
    <price>20</price>
    <available_count>10</available_count>
    <remarks></remarks>
</product>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P2</product_id>
    <price>40</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1002</supplier>
    <product_id>1002-P1</product_id>
    <price>20</price>
    <available_count>8</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1002</supplier>
    <product_id>1002-P2</product_id>
    <price>30</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1003</supplier>
    <product_id>1003-P1</product_id>
    <price>40</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1003</supplier>
    <product_id>1003-P4</product_id>
    <price>10</price>
    <available_count>50</available_count>
    <remarks></remarks>
</product>
</products>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/products/product')
      WITH (supplier        varchar(50)        'supplier',
            product_id        varchar(50)        'product_id',
            price            varchar(50)     'price',
            available_count varchar(50)     'available_count',
            remarks            varchar(50)     'remarks') order by product_id
EXEC sp_xml_removedocument @idoc

 

 

I need to call above query output to below query....

 

;With CTE as
(

---- here i need to call the above query (OPENXML ) output how?

 )
,
ROWNUM
as
(
select P.product_id,P.product_name,isnull(CTE.price,0)as price,CTE.remarks ,
ROW_NUMBER() over(partition by p.product_id order by price,case when isnull(remarks,'') <>'' then 0 else 1 end) as rnum from CTE
right JOIN  @Product p on ( CTE.product_id=P.supplier_one
Or CTE.product_id=P.supplier_two  Or CTE.product_id=P.supplier_three ) where P.product_id = 1

)
select product_id,product_name,price,remarks from ROWNUM

c where rnum=1
order by Case when price=0 then 1 else 0 end ,price,product_name

 

Answer 1

you mean like this

 

SET NOCOUNT ON DECLARE @Products TABLE (product_id VarChar(50),product_name VarChar(50), supplier_one VarChar(50), supplier_two VarChar(50), supplier_three VarChar(50))
INSERT INTO @Products Values ('1','Pen','1001-P1','1002-P1','1003-P1');
INSERT INTO @Products Values ('2','Pencil','1001-P2','1002-P2','1003-P2');
INSERT INTO @Products Values ('3','School Bag','1001-P3','1002-P3','1003-P3');
INSERT INTO @Products Values ('4','Book','1001-P4','1002-P4','1003-P4');
INSERT INTO @Products Values ('5','Pencil Box','1001-P5','1002-P5','1003-P5');

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
<products>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P1</product_id>
    <price>10</price>
    <available_count>10</available_count>
    <remarks></remarks>
</product>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P1</product_id>
    <price>20</price>
    <available_count>10</available_count>
    <remarks></remarks>
</product>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P2</product_id>
    <price>40</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1002</supplier>
    <product_id>1002-P1</product_id>
    <price>20</price>
    <available_count>8</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1002</supplier>
    <product_id>1002-P2</product_id>
    <price>30</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1003</supplier>
    <product_id>1003-P1</product_id>
    <price>40</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1003</supplier>
    <product_id>1003-P4</product_id>
    <price>10</price>
    <available_count>50</available_count>
    <remarks></remarks>
</product>
</products>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

--EXEC sp_xml_removedocument @idoc

;With CTE as 
(

SELECT *
FROM OPENXML (@idoc, '/products/product')
      WITH (supplier        varchar(50)        'supplier',
            product_id        varchar(50)        'product_id',
            price            varchar(50)     'price',
            available_count varchar(50)     'available_count',
            remarks            varchar(50)     'remarks') --order by product_id
 )
,
ROWNUM
as
(
select P.product_id,P.product_name,isnull(CTE.price,0)as price,CTE.remarks ,
ROW_NUMBER() over(partition by p.product_id order by price,case when isnull(remarks,'') <>'' then 0 else 1 end) as rnum from CTE
right JOIN  @Product p on ( CTE.product_id=P.supplier_one 
Or CTE.product_id=P.supplier_two  Or CTE.product_id=P.supplier_three ) where P.product_id = 1 

)
select product_id,product_name,price,remarks from ROWNUM 

c where rnum=1
order by Case when price=0 then 1 else 0 end ,price,product_name

EXEC sp_xml_removedocument @idoc

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Try the xml datatype and it's methods instead of OPENXML, it's simpler to use, more flexible and doesn't include the potential memory issues of OPENXML, eg

SET NOCOUNT ON 
DECLARE @Products TABLE (product_id VarChar(50),product_name VarChar(50), supplier_one VarChar(50), supplier_two VarChar(50), supplier_three VarChar(50))
INSERT INTO @Products Values ('1','Pen','1001-P1','1002-P1','1003-P1');
INSERT INTO @Products Values ('2','Pencil','1001-P2','1002-P2','1003-P2');
INSERT INTO @Products Values ('3','School Bag','1001-P3','1002-P3','1003-P3');
INSERT INTO @Products Values ('4','Book','1001-P4','1002-P4','1003-P4');
INSERT INTO @Products Values ('5','Pencil Box','1001-P5','1002-P5','1003-P5');

DECLARE @xml XML
SET @xml ='<products>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P1</product_id>
    <price>10</price>
    <available_count>10</available_count>
    <remarks></remarks>
</product>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P1</product_id>
    <price>20</price>
    <available_count>10</available_count>
    <remarks></remarks>
</product>
<product>
    <supplier>1001</supplier>
    <product_id>1001-P2</product_id>
    <price>40</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1002</supplier>
    <product_id>1002-P1</product_id>
    <price>20</price>
    <available_count>8</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1002</supplier>
    <product_id>1002-P2</product_id>
    <price>30</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1003</supplier>
    <product_id>1003-P1</product_id>
    <price>40</price>
    <available_count>5</available_count>
    <remarks>1</remarks>
</product>
<product>
    <supplier>1003</supplier>
    <product_id>1003-P4</product_id>
    <price>10</price>
    <available_count>50</available_count>
    <remarks></remarks>
</product>
</products>'

SELECT 
	p.c.value('(supplier/text())[1]','INT') AS supplier,
	p.c.value('(product_id/text())[1]','VARCHAR(50)') AS product_id,
	p.c.value('(price/text())[1]','MONEY') AS price,
	p.c.value('(available_count/text())[1]','INT') AS available_count,
	p.c.value('(remarks/text())[1]','INT') AS remarks
FROM @xml.nodes('products/product') p(c)