I've an xml which has multiple blocks in it as per below.
declare @Query as xml,
@poxmldoc INT
set @Query='<ProductionOrder><Header><MessageID>00000005</MessageID></Header><Body>ProductionOrderDetails>Workorder_ID>100</Workorder_ID<Item_ID>4010124</Item_ID<Publisheddata></Publisheddata><BOM><Position>1</Position><Item_ID>111</Item_ID><BOM_Qty>100</BOM_Qty><UoM>Liters</UoM>
</BOM><BOM><Position>2</Position><Item_ID>222</Item_ID><BOM_Qty>101</BOM_Qty><UoM>Kilograms</UoM></BOM><BOM><Position>3</Position><Item_ID>333</Item_ID><BOM_Qty>102</BOM_Qty><UoM>Kilograms</UoM></BOM></ProductionOrderDetails><ProductionOrderDetails><Workorder_ID>101</Workorder_ID><Item_ID>4010124</Item_ID><Publisheddata></Publisheddata><BOM><Position>1</Position><Item_ID>111</Item_ID><BOM_Qty>103</BOM_Qty><UoM>Liters</UoM></BOM><BOM><Position>2</Position><Item_ID>222</Item_ID><BOM_Qty>104</BOM_Qty><UoM>Kilograms</UoM></BOM><BOM><Position>3</Position><Item_ID>333</Item_ID><BOM_Qty>105</BOM_Qty><UoM>Kilograms</UoM></BOM></ProductionOrderDetails></Body></ProductionOrder>'
When I execute this query
EXEC SP_XML_PREPAREDOCUMENT @poxmldoc OUTPUT, @Query
SELECT *
FROM OPENXML(@poxmldoc, '/ProductionOrder/Body/ProductionOrderDetails/BOM',2)
WITH (Position INT 'Position',
Item_ID NVARCHAR(40) 'Item_ID',
BOM_Qty FLOAT 'BOM_Qty',
UoM NVARCHAR(40) 'UoM')
I get this output:
Position Item_ID BOM_Qty UoM
------------------------------------
1 111 100 Liters
2 222 101 Kilograms
3 333 102 Kilograms
1 111 103 Liters
2 222 104 Kilograms
3 333 105 Kilograms
How to get the <Workorder_ID> tag value along with this result.
Like this:
Position Item_ID BOM_Qty UoM Wo_Id
---------------------------------------------
1 111 100 Liters 100
2 222 101 Kilograms 100
3 333 102 Kilograms 100
1 111 103 Liters 101
2 222 104 Kilograms 101
3 333 105 Kilograms 101
No comments:
Post a Comment